Re: DB design for storing family tree information

Your design will need a better, or an arbitrary, primary key - since names will repeat. Additionally, you should refer to people as people, not ancestors since you will eventually use the same tables for your *descendants*. Obviously, you will also need to record the descent tree itself, which at its simplest gives you the following:

1. PEOPLE

   ---

   Key <arbitrary> Forename Surname (you will need it for marriages etc) <other, eg birth and death dates>
2. MARRIAGES

   ---

   Bride <ref: PEOPLE> Groom <ref. PEOPLE> <other data, eg. date of marriage / divorce>
3. CHILDREN

   ---

   Father <ref. PEOPLE> Mother <ref. PEOPLE> Child <ref. PEOPLE>

Note that no other data is required in the CHILDREN table.

On Fri, 14 Jan 2000 11:03:23 -0500, "P.A." <john.doe_at_nospam.org> wrote:

>Hi,
>
>I am trying to design a small DB to store my family tree information. I
>have come up with a design as follows,
>
>Ancestors
>---------
>Name NOT NULL (PK)
>Father (FK)
>Mother (FK)
><other-info>
>
>Foreign key (Father,Mother) into Couple table
>
>Couples
>-------
>Husband NOT NULL (PK) (FK) -- refers to a row in Ancestors
>Wife NOT NULL (PK) (FK) -- refers to a row in Ancestors
><other-info>
>
>I was happy with this design and then I read a very interesting thread,
>(unfortunately only partially thanks to my news-server) on BOM where Mr.
>Celko gave an interesting alternative using "SET". Now family tree
>information is not exactly a single tree hierarchy, but more a DAG
>(Directed Acyclic Graph), since both the mother and the father have a
>tree. I was wondering are there any better (or just interesting) ways to
>design this DB ?
>
>TIA
>
>P.A.
>
>P.S. On BOM thread, there was another interesting post by, (I apologize
>if I am mistaken) Mr. Kyle, but unfortunately it was removed from my
>server before I had a chance to read it completely. What was his
>approach - if you don't mind repeating it.
 

-- 
Mark A Preston BSc, FIAP
Business Manager, MicroFix Systems Solutions
mark_at_mpreston.demon.co.uk