Idea To Approach Sql Family Tree (mysql)

I currently have 100k+ records in my mysql database that define a person. A person has an ID, a fatherID, a motherID and a partnerID. I would like to add treeID's to each record so

Solution 1:

A lot of work has been put into genealogy data structures. Take a look at GEDCOM standards and how they define and connect various data elements. Those tend to be stored in flat files instead of databases, but the objects still have a fairly relational structure.

In general, it's a bit more complicated than your current structure is expressing. But if you extract a slightly different entity structure it starts to come together nicely. Currently you have a Person which is supposed to know all about its connections. Instead, make the connections themselves into a structure. Something like this:

Person
----------
ID
Name
etc.

Family
----------
FatherID
MotherID

Child
----------
FamilyID
PersonID

This should at a basic level at least cover blood relationships. It's safe to assume that any given person was created by means of one father and one mother. Their ongoing family structure may be quite different, but the physical act of creating a child is pretty standard and well documented. For families where the father or mother is unknown, those columns can just be nullable.

From there you can extend the structure to include additional features. For example, perhaps a person was born of one family but adopted to another. Then you can add a Type flag to the Child table:

Child
----------
FamilyID
PersonID
TypeID

Types may include things like Birth, Adoption, Godparents, etc. To include children raised by same-sex couples you might just rename the Family fields:

Family
----------
Parent1ID
Parent2ID

If you add a Gender to Person then you can infer the "father" and "mother" where necessary. (For families with more than two parents at any given time, get more imaginative I suppose.) This can also handle families with deaths/divorces and re-marriages. A Person can be a Child of multiple Family entities, with different Type flags. And a Person can be a Parent in multiple Family entities, perhaps with other distinguishing data.

Such distinguishing data might include dates of events. Maybe something like this:

FamilyEvent
----------
ID
EventTypeID
FamilyID
DateOccurred

So a Family might have a "Marriage" event, or might not. Might have a "Divorce" event. And so on. It's also reasonable that a Person can have events, such as "Birth" and "Death" (and any number of important events in between):

PersonEvent
----------
ID
EventTypeID
PersonID
DateOccurred

(You might use table sub-typing to generalize that a bit, but I don't think it's necessary. The complexity introduced would make further expansion more difficult, and believe me this can further expand a lot as you discover the cultural differences among people and families in a significantly large genealogical structure.)