Re: DB design for storing family tree information

On Tue, 18 Jan 2000 12:52:53 -0500, "P.A." <john.doe_at_nospam.org> wrote:

>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 ?
>
No - its too simple to normalise much (the data is all in the PEOPLE table and the MARRIAGES one - CHILDREN is just a cross-reference index). I admit that, strictly speaking MARRIAGES should have only an arbitrary key and there should be another table that holds that key and two PEOPLE keys as an index between MARRIAGES and 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.)
>
Oh definately! You can (and will) get examples like my own ancestor John Preston of Gisburn (1684 - 1751). He had a bunch of kids by one woman and after her death married another. During that marriage, one of his sons died and his daughter-in-law came to live at the family house. A son was *registered* to John and Margaret (his wife) but when we looked for the details, we found a record of birth from his daughter-in-law, who continued to live at the house after the death of John's second wife and who had a further two children by him. How would you record that (and note that this is also why I used MARRIAGES rather than COUPLES for the table name)?
>
>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
 

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