How to create a dynamic tree like structure

I have the details of the members of my family in a database. I want to generate a web p开发者_StackOverflow社区age with a family tree generated dynamically by reading from the table.

My table schema looks like this

id(int)  name  father(int)  mother(int)  spouse(int)  dateOfBirth

where father, mother, and spouse are referencing the id column of the same table. The root node will have null for father and mother.

Given this data how can I go about dynamically generating the family tree. I am new at designing tables, so if this design is sub optimal kindly suggest another schema from which this objective can be achieved.

Any pointers on how to atleast get started would be highly appreciated.

Take a look at nested set model.

Your design looks ok, but with this design it's easier to insert nodes than to get them out of the table.

You can look at nested sets and implement that model. Nested sets are harder to update, but you can get the nodes of any subtree with a single query, so I think it matches your problem quite well (a family tree doesn't change too often :).

You would need some metadata, like relation type (Child, Sibling, Spouse) in addition to the nested sets parent-child relations, but I think you can add that easily.

This design is ok, but you would either select all the data and then build the tree on client side iteratively checking the returned array, or perform many subqueries, which is also not very good.

The best solution I know (for hierarchical structures, you've got spouses also) is storing the tree path in a string field. For example, you've got grandpa with id=1, children with id=2 and 3, 2 has children 4 and 5. Then, they have paths, respectively, "", "1", "1", "1,2", 1,2".

You can use this structure to retrieve the tree elements in order, using ORDER BY path clause.