Re: DB design for storing family tree information

Yes, you are right, people with same name do pose a problem, if name is the primary key. BTW, thanks for your suggestions on more meaningful names.

Also, isn't your design, if I may say, a bit "un-normalized". You can insert into CHILDREN table with any combination of mother and father. Shouldn't Father+Mother foreign-key back into MARRAIGES (or COUPLES - a more politically correct name) instead of PEOPLE ?

Secondly, are there any reasons in theory or practice for maintaining separate CHILDREN table as opposed to putting Father+Mother columns in the PEOPLE table ? (Only the persons appearing at the top of the hierachy will have those values as NULL.)

P.A.
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 Tue Jan 18 2000 - 00:00:00 CET