Re: DB design for storing family tree information

Some more comments regarding CHILDREN table below: You can put the data into the PEOPLE table since no child has more than one father and one mother (at least biologically). Consider allowing nulls for the father and mother columns, since you may not have complete data for all ancestors. Children are not necessarily related to marriages as the OP proposed ;-)

Heinz

Mark Preston wrote:
>
> 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
Received on Mon Jan 17 2000 - 00:00:00 CET