Re: DB design for storing family tree information

Hi!

"P.A." wrote:
>

 [snip]
>
> 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 ?

See my other post. There is the possibility that you know the mother but not the father. For very old data it may even be the other way ;-)

>
> 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.)

Well, some reasons could be e.g. adoption or step-parents. As long as you only have to consider biological parents, you can stick with Father + Mother columns in PEOPLE.

>
> 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.
> >
[snip]

Heinz Received on Wed Jan 19 2000 - 00:00:00 CET