How to structure a family tree using PHP and mysql -

[ofmyst]

Now that I have a very basic understanding of mysql and php I would like to use it for my website that contains my famiily tree.  Each page is a branch of the tree - the parents and their children.  When you click on one of the children they become the parents and their children are listed. This goes on down the line.  It is very unwieldly right now, as I manually write each page and every change has to be reflected in the next generation page. 

 

Is there a good way to structure mysql to make this function efficiently?  Right now we go as far as five generations.  My instinct is to have a table within a table but I do not know if this can be done.  And if so if it is the most efficient way to handle the task at hand. 

 

Any help will be very much appreciated.

[Fadion]

The most basic approach could be with a single table like this:

 

id
name
parent

 

Parent has the id of the user (foreign key on primary key of the same table :0), so a parent 0 means no parent, while a parent with a specified id means that this person has one other person as a parent. Relationships can be built upon it and a recursive approach can show the whole tree. Expanding it, you can add "married_to" to show also the husband/wife of the person.

 

id
name
parent => parent:0 = no parent; parent:-1 = married with someone of the family
married_to => married_to: 0 = single, no children; married_to: xx (userid) = married to someone

 

You'll have two fields which point to the id (primary key of the table) and i'm not sure this is a good practice. Even though this could be a simple solution.

[ofmyst]

I am sorry, but I don't exactly understand how the database table structure would work to accomodate the next generations.  How would the fields be constructed?  Thank you!

[Fadion]

I did that post 1 week ago  ???

 

Anyway, the parent field specifies the parents, basically:

 

ID:1; Name:John; Parent:0

ID:2; Name:Smith; Parent:1

ID:3; Name:Ben; Parent:1

ID:4; Name:James; Parent:3

 

So, Smith and Ben are children of John, while James is the child of Ben. In this way you can have unlimited generations and loop through them recursively.

[ofmyst]

I'm sorry it was a week.  Life threw some curve balls my way!  Okay, I think I get the gist of it.  I'm very new to mysql and php so it usually takes me alot of trial and error, but mostly help from the kindness of others.  Thank you.

[ofmyst]

I have tried to make this work, but with parents, children, their children, etc., and starting from first generalation having seven children, it is becoming very unwieldy from an organizational standpoint and tracking who goes with who in the mysql.  Is there perhaps another way of approaching this that keeps the families "together" in the database?