How to store family tree data in a mysql database

Question

I have a family tree. I would like to store it in a mysql database. I have a table with a column called "family members," but i don't know how to arrange these family members. For example, I am under my dad and my son is under me. So i guess, how can i store this type of tree in a database?

Answer 1

So, you said you have a table with a column called "family members". For me, that's just inappropriate because it doesn't respect normalization :) First of all I would call it "familyTreeId". Now, let's move to the FamilyTree table.

This table would be something like this:

FamilyTree(id, motherId, fatherId, etc) --> etc: if you have additional data

• id will be the primary key of the table
• motherId will link to the row in the FamilyTree table that belongs to the mother
• fatherId will link to the row in the FamilyTree table that belongs to the father

So the rows will be:

+--------+--------------+--------------+
|   id   |   motherId   |   fatherId   |
+--------+--------------+--------------+
| son1   | yourwife     | you          |
| son2   | yourwife     | you          |
| you    | mother       | father       |
| mother | grandmother1 | grandfather1 |
| father | grandmother2 | grandfather2 |
+--------+--------------+--------------+

Other option would be to store the couples

FamilyTreeParents(id, motherId, fatherId)

FamilyTreeNodes(id, familyTreeParentsId)

• id will be the primary keys of the tables
• familyTreeParentsId will be a foreign key to a FamilyTreeParents table
• motherId will be a foreign key to a row in the FamilyTreeNodes table that belongs to the mother
• fatherId will be a foreign key to a row in the FamilyTreeNodes table that belongs to the father

So the rows will be:

FamilyTreeParents

+----+--------------+--------------+
| id |   motherId   |   fatherId   |
+----+--------------+--------------+
|  1 | yourwife     | you          |
|  2 | mother       | father       |
|  3 | grandmother1 | grandfather1 |
|  4 | grandmother2 | grandfather2 |
+----+--------------+--------------+

FamilyTreeNodes

+--------+---------------------+
|   id   | familyTreeParentsId |
+--------+---------------------+
| son1   |                   1 |
| son2   |                   1 |
| you    |                   2 |
| mother |                   3 |
| father |                   4 |
+--------+---------------------+

Data is more normalized this way because you are not repeating information (like you and yourwife for son1 and son2 as I did in the other solution. However, this solution might be less efficient in terms of speed because there will be needed more joins.