Re: DB design for storing family tree information

"P.A." 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.

If your family is like mine, using name as a primary key won't cut it. The same
first names tend to be repeated time and time again. I have an even odder situation in that I have both a son and a cousin named Mark, both of whom are married to women named Barbara.

Go to deja.com and search for celko and I think you will be able to find the
entire thread.

hth

--
Jerry Gitomer
Once I learned how to spell DBA, I became one.