lwk@caen.engin.umich.edu (Woody Kellum) (03/02/90)
A while back I posted a request for a schema to do a genealogical database, and promised to post replies. Well, I got one reply from Dr. Alan Kent (ajk@goanna.cs.rmit.oz.au) with the following schema: People(pid, name, fatherpid, motherpid, adoptedfatherpid, adoptedmotherpid, dateofbirth, datechristened, dateofdeath, sex, etc) Marriages(mid, husbandpid, wifepid, dateofmarriage, dateofdivorce, etc) I actually have a large text field for additional notes in each table. This seemed better than enumerating all of the possible fields you ever want. Extracting at least the above fields means you can do queries on date (or order by date). Separating the Marriages table also meant that there did not have to be a marriage record for a person to have parents. You get into trouble storing a 'mid' in People instead of the mother and father pid's separately. If you only know someones father and not mother, you can leave the motherpid set to NULL. I have not found anyone yet with more than one set of adopted parents. Using names for key values is a big mistake, as you frequently need to change the name field (e.g. you find out someone's middle name later). The above schema has worked well for me. I currently have over 1000 people in my own database. The real fun is the program to print it out nicely!! I am currently trying to rewrite it to include pictures of peoples faces. Getting there.... Thank you, Dr. Kent