Relational database for family tree

Question

I would like to build a MySQL database to store genealogy data. It will then be output to a website using PHP. The family tree will only consist of direct ancestors and be of an inverted pyramid type : my daughter as the source, her 2 parents as the 2nd generation, her 4 grandparents as the 3rd generation, her 8 greatgrandparents as the 4th generation etcetera.

I was thinking of creating four tables, one for the male ancestors, a second one for the female ancestors and then another two tables for the relationships between them.

All entries in table "male" and "female" would have an exclusive ID as primary key and basic data about each individual (first name, last name, generation, birth date and place, death date and place, etc). I have created these two tables, populated them with data and know how to query them. What I am unsure about is how to connect tables "male" and "female" in order to make the connections with regards to marriages, ascendants and descendants.

This is what I've done so far.

CREATE DATABASE tree;

CREATE TABLE male ( sosa CHAR(20), firstname VARCHAR(128), lastname VARCHAR(128), birthplace VARCHAR(128), birthday SMALLINT, birthmonth SMALLINT, birthyear SMALLINT, deathplace VARCHAR(128), deathday SMALLINT, deathmonth SMALLINT, deathyear SMALLINT, note VARCHAR(128), generation SMALLINT, PRIMARY KEY (sosa)) ENGINE MyISAM;

CREATE TABLE female ( sosa CHAR(20), firstname VARCHAR(128), lastname VARCHAR(128), birthplace VARCHAR(128), birthday SMALLINT, birthmonth SMALLINT, birthyear SMALLINT, deathplace VARCHAR(128), deathday SMALLINT, deathmonth SMALLINT, deathyear SMALLINT, note VARCHAR(128), generation SMALLINT, PRIMARY KEY (sosa)) ENGINE MyISAM;

I then created two tables to display the relationships :

CREATE TABLE marriages ( father CHAR(20), mother CHAR(20), marriageplace VARCHAR(128), marriageday SMALLINT, marriagemonth SMALLINT, marriageyear SMALLINT, note VARCHAR(128)) ENGINE MyISAM;

CREATE TABLE child ( childId CHAR(20), father CHAR(20), mother CHAR(20)) ENGINE MyISAM;

Is my database structure sound, or am I overcomplicating ? For each individual listed, I need to be able to display : who are his/her parents, who are his/her children, who are his/her ancestors over a time span of x generations, for instance 3 generations down the line.

EDIT :

Thanks for the idea of putting all people in one table, I'll give it a try.

As for the auto increment, I would rather use the commonly used Sosa reference in family trees. This gives the number 1 to the source of the family tree, in this case my daughter, 2 for her father and 3 for her mother. Grandparents in the next generation get the numbers 4, 5, 6 and 7. To find the father for any person, you just double the number (2 is the father of 1, 4 is the father of 2 etc). To find the mother, you double the number and add one (3 is the mother of 1, 5 is the mother of 2). Auto increment could work, but when you can't trace certain ancestors, because certain branches go dead, you're left with unused reference numbers.

Answer 1

The GEDCOM data model and the Gramps data model are two of the most popular formats for exchanging geneological data between different tools. Using either of these data models should both (1) make your tool more compatible with other tools and (2) ensure that your data model is compabible with many special cases, considering both data models are specially designed to deal with geneological data.

Tools like Oxy-Gen or the Gramps PHP exporter should get you on your way with respect to how to import GEDCOM data into a database.

For more details, see also my answer to “Family Tree” Data Structure.

Answer 2

I would just have a single table for people, with each row having a way of referencing the parents. Unless you want to cope with less usual situations then probably just a column for mother and a column for father.

You can then read the table by recursively doing selects

MySQL does not support native recursive queries, so you would probably have to do multiple queries. You can use the nested set model to get a tree structure, but in this situation (where possibly people can be parents to multiple children in different branches of the tree) I don't think this would be suitable.

A relationship table for marriages would still make sense.

As an aside, it is best generally to use auto incremented integer id fields to refer to records on other tables (not always, but in this case it would be), and if doing so store this id in an INT field in other tables that refer to it.