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.

This might be fine for your family, and for families in general that follow a traditional model, but I'm not sure it would work so well for soap opera families or less extreme, real-world variations thereof. And why MyISAM?? — Strawberry, Apr 22 '15 at 07:30
Thanks for your comment. The model would be fine for my family, the only thing I've encountered so far as I go back several generations is people being married two or three times, which was not so exceptional a few centuries ago, mainly due to early deaths. As for MyISAM, I don't really know what else to put, I am only starting out with databases. I'd like to make sure I build it the right way before starting to introduce larger amounts of data. I see what you mean about date types, but I'd also like to search for patterns in birth and death dates, for instance by month. — Jan, Apr 22 '15 at 08:39
The search - whether for months, days, or years makes no difference to the choice of data type. A date data type will be more efficient. — Strawberry, Apr 22 '15 at 12:23
Trouble with a meaningful number like the sosa reference is that it is very limiting, restricting the tree down to a particular final individual. For example what would the number be for another child you have? Idea of the auto increment is that it is meaningless (so gaps are irrelevant) beyond pointing to another individual. If you wanted you could add a relationship table that stores the 2 unique ids with the sosa reference for their relationship. — Kickstart, Apr 24 '15 at 10:56
possible duplicate of [MySQL Store Relationship (Family) Tree](http://stackoverflow.com/questions/5773938/mysql-store-relationship-family-tree) — John Slegers, Jul 03 '15 at 22:08

score 1 · Answer 1 · edited May 23 '17 at 12:23

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.

score 0 · Answer 2 · answered Apr 22 '15 at 09:42

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.

Relational database for family tree

2 Answers2