CODEWITHSUNDEEP
phpjavascript
Eby John
Eby John

Reputation: 135

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 page 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.

Upvotes: 3

Views: 1941

Answers (3)

Slava
Slava

Reputation: 2050

Take a look at nested set model.

Upvotes: 2

vissi
vissi

Reputation: 2334

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.

Upvotes: 0

Daniel Dinu
Daniel Dinu

Reputation: 1791

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.

Upvotes: 0

Related Questions