Inbreeding-immune database structure
- by Nick Savage
I have an application that requires a "simple" family tree. I would like to be able to perform queries that will give me data for an entire family given one id from a member in the family. I say simple because it does not need to take into account adoption or any other obscurities. The requirements for the application are as follows:
Any two people will not be able to breed if they're from the same genetic line
Needs to allow for the addition of new family lines (new people with no previous family)
Need to be able to pull siblings, parents separately through queries
I'm having trouble coming up with the proper structure for the database. So far I've come up with two solutions but they're not very reliable and will probably get out of hand quite quickly.
Solution 1 involves placing a family_ids field on the people table and storing a list of unique family ids. Each time two people breed the lists are checked against each other to make sure no ids match and if everything checks out will merge the two lists and set that as the child's family_ids field.
Example:
Father (family_ids: (null)) breeds with Mother (family_ids: (213, 519)) ->
Child (family_ids: (213, 519)) breeds with Random Person (family_ids: (813, 712, 122, 767)) ->
Grandchild (family_ids: (213, 519, 813, 712, 122, 767))
And so on and so forth... The problem I see with this is the lists becoming unreasonably large as time goes on.
Solution 2 uses cakephp's associations to declare:
public $belongsTo = array(
'Father' => array(
'className' => 'User',
'foreignKey' => 'father_id'
),
'Mother' => array(
'className' => 'User',
'foreignKey' => 'mother_id'
)
);
Now setting recursive to 2 will fetch the results of the mother and father, along with their mother and father, and so on and so forth all the way down the line. The problem with this route is that the data is in nested arrays and I'm unsure of how to efficiently work through the code.
If anyone would be able to steer me in the direction of the most efficient way to handle what I want to achieve that would be tremendously helpful. Any and all help is greatly appreciated and I'll gladly answer any questions anyone has. Thanks a lot.
