13

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?

kirby
  • 3,981
  • 14
  • 41
  • 54
  • 2
    See the article: [Storing Hierarchical Data in a Database](http://www.sitepoint.com/hierarchical-data-database/) – ypercubeᵀᴹ Feb 06 '12 at 07:07
  • 1
    and this one: [Managing Hierarchical Data in MySQL](http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/) – ypercubeᵀᴹ Feb 06 '12 at 07:08
  • and a presentation: [Models for hierarchical data](http://www.slideshare.net/billkarwin/models-for-hierarchical-data) – ypercubeᵀᴹ Feb 06 '12 at 07:08
  • and an excellent book by Joe Celko: [Trees and Hierarchies in SQL for Smarties](http://www.amazon.com/dp/1558609202/) – ypercubeᵀᴹ Feb 06 '12 at 07:12
  • I think that the [Nested Set model](http://en.wikipedia.org/wiki/Nested_set_model) behaves quite well with MySQL. See this comparison: [Adjacency list vs. Nested sets: MySQL](http://explainextended.com/2009/09/29/adjacency-list-vs-nested-sets-mysql/) – ypercubeᵀᴹ Feb 06 '12 at 07:13
  • perhaps too late to be useful, i have added an answer, please give your comments since i'm also busy with planning such a schema – peter Feb 25 '12 at 13:43
  • Yes, use Nested Sets. Also - don't implement it yourself! If you would specify the language you intend to use with this database - presuming that you're not using a standalone solution such as Access - you should find that there are good implementations of this already. For example, if you're using PHP, have a look at the Propel ORM. – halfer Feb 25 '12 at 13:45
  • 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:06

4 Answers4

13

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.

Kami
  • 19,134
  • 4
  • 51
  • 63
Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123
  • 3
    Good detail! However there are a couple of technical assumptions here that might make for inaccurate genealogical records. Your table 'FamilyTreeParents' assumes that a family is made up of two parents - what about families with one (very common) or more than two (occurrence > 0)? There's some gender assumptions here too: what about a gay couple (of either gender) who adopt - perhaps partner1Id and partner2Id would be an improvement here? – halfer Feb 25 '12 at 13:57
  • 3
    what to do with children from previous marriage etc. ? – JP Hellemons Mar 28 '13 at 08:07
  • How can we define relation with wife? – Prerana Nov 07 '21 at 07:18
2

I would keep two tables, one with persons, other with relations. Question here is if you should keep the realtion in one record (eg husband - wife) or also from the other person's view (1:husband - wife, 2:wife - husband) Advantage of second approach is quick searches so fast rendering of eg a layout but also larger table with more writes when data change and possible errors. I would take the first approach and use some index to make the searches quicker.

So with a minimum of connections you could write out the following family

grandfather louis(id1) 
  x grandmother clothild(id2)
    father francois(id3) 
      x mother diana(id4)
        me peter(id5) 
          x my first wife fabienne(id6)
            my son laurent(id9)
          x my second wife jane(id7)
            my son tristan(id10)
        my brother hans(id8)

as

1x2
3x4
5x6
5x7
1>3
2>3
3>5
4>5
3>8
4>8
6>9
5>9
5>10
7>10

or shorter

1x2>3
3x4>5
3x4>8
5x6>9
5x7>10

So in a databasetable this gives

id_partner1 id_partner2 id_child
1           2           3
3           4           5
3           4           8
5           6           9
5           7           10
peter
  • 41,770
  • 5
  • 64
  • 108
1

You can have schema like this

Family( Parent_name, Child_name ). The "tuple" (Parent_name, Child_name) are the key of your table. Assuming there is no duplicate (Parent_name, Child_name) exist in your family tree. If you have anything like Social Security Number to uniquely identify a person in the family tree, then you should the Parent_ssn, Child_ssn instead of names and have a separate table to store the relation between ssn and name, whose key would be ssn

items in this table can be

[Your dad, you]
[Your mum, you]
[you, your son]
[you, your 2nd son]
[your wife, your son]

Hope this helps

louis.luo
  • 2,921
  • 4
  • 28
  • 46
0

The schema can be this:

id person related_person relation comments
1 Mac Mac's Brother Brother-Brother
2 Mac' mother Mac Mother-Son
3 Mac Mac' mother Son-Mother actually same as 2

Support more relationships, even ex-wife and ex-husband.

Also cost-saving, only one row is required between any two people, because their relationship can be reversed.

MARK: It is feasible for a small amount of data.

eeeeirkkkk
  • 1