So I am a newbie 1

[Dostani]

I am in the process of creating a family geneology site. I am not sure how to approach the table relations or if they will behave like Access. Can I relate a parent table to several child tables using the "FirstGeneration" name as the key throughout the 10 generation tables?? if so how?


Or point me to the proper structure reference....PLEASE!

Dostani

 

[ericbrunson]

I'm not sure how access works and you description is a little ambiguous, so I'll speak from basic tennets.

You want to model a tree structure, so the easiest way to do this is with a single table using a self-referencing foreign key relationship.

In an SQL schema parent records do not reference their children, rather the child records maintain a pointer back to their parents (very apt descriptions in a geneology database). So if I have a departmant table and an employee table I may have:

Table:  Department
id Name
-- ----
 1 Engineering
 2 Management

Table:  Employee
id dept Name
-- ---- ----
 1    1 Joe Engineer
 2    1 Sue Engineer
 3    2 Bob Manager

So, you see that each employee record contaings the id of the department the employee belongs to.

In modeling a tree structure with undetermined depth the schema which lends itself most flexibly is a single table with a foreign key into itself. Let's say for simplicity we're only interested in the father relationship:

Table: People
id dad  Name
-- ---- ----
 1 null Great Great Grandpa Joe
 2    1 Great Grandpa Mike
 3    2 Grandpa Jeff
 4    2 Great Uncle Bob
 5    2 Great Aunt Thelma
 6    3 Dad
 7    3 Uncle Pat
 8    6 Me
 9    6 My Brother
10    7 Cousin Heather

Can you see how that works? Adding a second foreign key for mother is trivial.

Marriages, since with death, divorce and remarriage there can be a many to many relationship, would require a cross-reference table which would contain ancillary information about the union (of souls) such as date of marriage, date of divorce, etc.

 

[ericbrunson]

You may also want to consider the nested set model, rather than the adjacency model I described above. There's an excellent article on it here:

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

 

[Dostani]

Thanks Eric,

Just what I needed!!!

Thanks for your reply!!

Dostani