Geek asks and answers

Saving tree data in a database (family tree)

I am trying to preserve the family tree. Here is the platform I'm using, Zend framework, Mysql, Ajax I was looking for stackoverflow. I came across this post, which is very useful when processing data in terms of objects.

Family Tree Data Structure

I will briefly illustrate my use case. The user can create family members or friends based on several relationships defined in the database. I also have a model for relationships. The user can create family members, such as a divorced spouse, friezes. Max Tree may be deep, that we accept max for children of grandchildren, but it can also expand in width. Brother / sister and their family.

I am looking for an efficient database design for less query time. If I need to use the data structures described above, where should I store them, since they should be a model.

For the presentation, I plan to use visualization: an organization chart from http://code.google.com/apis/chart/interactive/docs/gallery/orgchart.html#Example

I will summarize what I need

  • Database design
  • Location of controllers (ajax) and models
  • People created by the user will not be other users. just some other data

Yes it is! I will lay out a complete solution on this topic when I complete the project, of course, with the help of expertise u guys

Thanks in advance

EDIT I will do my best to clarify my situation.

, /

Family

ID        userid              relation id             Name

1         34                   3 // for son             ABC
2         34                   4 // for Wife            XYZ
3         34                   3 // for Mom             PQR
4         34                   3 // for DAd             THE
5         34                   3 // for Daughter        GHI
6         34                   3 // for Brother         KLM

, , - .

, , , , , .. - -.

. .

, , .

+3
1

, ,

id | name | parent_male | parent_female


id | name | prefix 
1  | Joe  | 0001
2  | Jack | 000100001 //ie. Joes son
3  | Marry| 0001 //ie. Jacks mother
4  | Eve  | 0002 // new family tree
5  | Adam | 00020001 // ie. Eves son
6  | Mark | 000200010001 // ie. Adams son

( ) , MPTT, , , ( ).

, - Mark grandparents:

--Mark
SELECT prefix FROM family_tree WHERE id = 6; 
-- create substring - trim N 4-character groups from the end where N is N-th parent generation => 2 for grandparent ==> 0002
--grandparents
SELECT * FROM family_tree WHERE prefix = '0002' 
-- same for other side of family
-- cousins from one side of family
SELECT * FROM family_tree WHERE prefix LIKE '0002%' AND LENGTH(prefix) = 12
+1

More articles:


All Articles