Idea of sql family tree approach (mysql)
I currently have 100k + records in my mysql database that identify a person. A person has an ID, a father ID, a mother, and a partner ID.
I would like to add treeID to each record so that I can distinguish between unrelated families. Although I can't seem to find a suitable approach, especially with mysql constraints.
If anyone has a good idea please enlighten me :)
source to share
A lot of work has been included in the structure of the genealogy data. Take a look at the GEDCOM standards and how they define and connect different data items. They are usually stored in flat files instead of databases, but objects still have a fairly relational structure.
In general, this is a little more complex than your current structure expresses. But if you extract a slightly different entity structure, it starts to blend well. By now, you have Person
someone who needs to know everything about their connections. Instead, create connections to the structure. Something like that:
Person
----------
ID
Name
etc.
Family
----------
FatherID
MotherID
Child
----------
FamilyID
PersonID
This should, at a basic level, at least cover blood relationships. It is safe to assume that any person was created with the help of one father and one mother. Their current family structure may be very different, but the physical act of creating a child is pretty standard and well documented. For families where the father or mother is unknown, these columns may not be valid.
From there, you can expand the structure to include additional functionality. For example, perhaps a person was born of one family, but was adopted into another. Then you can add the Type flag to the table Child
:
Child
----------
FamilyID
PersonID
TypeID
The types can include things like "Birth", "Adoption", "Goddess", etc. To include children raised by same-sex couples, you can simply rename the fields Family
:
Family
----------
Parent1ID
Parent2ID
If you add Gender
in Person
, then you can specify "father" and "mother" where appropriate. (For families with more than two parents at any given time, think about it, I guess). It may also apply to families with death / divorce and remarriage. A Person
can be Child
multiple objects Family
with different type flags. Or it Person
may be Parent
in several objects Family
, possibly with other distinctive data.
Such distinctive data may include dates of events. Maybe something like this:
FamilyEvent
----------
ID
EventTypeID
FamilyID
DateOccurred
So, it Family
may or may not have a "Marriage" event. May have a Divorce event. Etc. It is also reasonable that a Person
could have events such as "Birth" and "Death" (and any number of important events in between):
PersonEvent
----------
ID
EventTypeID
PersonID
DateOccurred
(You can use a table subtype to generalize this a bit, but I don't think it is necessary. The complexity introduced will make it more difficult to expand further and trust me that it can expand even further as you discover cultural differences between individuals and families in a significant large genealogical structure.)
source to share