Calculate Family Relationship from Genealogical Data

Question

I would like to be able to calculate the family relationship between two individuals in a family tree, given the following data schema (simplified from my actual data schema, only showing columns that directly apply to this problem):

individual
----------
id
gender

child
----------
child_id
father_id
mother_id

With this structure, how can one calculate the relationship between two individual id's (i.e. cousin, great grand uncle, etc.).

Also, as there are actually two relationships (i.e. A-B may be nephew whereas B-A is uncle), how can one generate the complement to the other (given uncle, and assuming we know gender, how might we generate nephew?). This is more of a trivial question, the former is what I'm really interested in.

Thanks all!

Answer 1

You'll first need to calculate the Lowest Common Ancestor of both A and B. Call this Lowest Common Ancestor C.

Then calculate the distance in steps from C to A (CA) and C to B (CB). These values should be indexed into another table that determines the relationship based on these two values. For example:

CA      CB      Relation
1       2       uncle
2       1       nephew
2       2       cousin
0       1       father
0       2       grandfather

You might keep the basic relations in this table, and add "great-" for additional distances on certain relations like grandfather, ex.: (0, 3) = great-grandfather.

Hopefully this will point you in the right direction. Best of luck!

UPDATE: (I can't comment below your code, since I don't have the reputation yet.)

Your function aggrandize_relationships is a little off, I think. You can simplify it by prefixing "grand" if the offset is 1 or greater, then prefix "great-" (offset - 1) times. Your version might include the prefix "great grand great grand" for very distant relatives.(Not sure if I have the correct parameter in this explanation, but hopefully you get the gist of it. Also, no idea if your family tree is going that far back, but the point remains valid.)

UPDATE TOO: Sorry, the above is incorrect. I misread the default case, and thought it recursively called the function again. In my defense, I wasn't familiar with the "2nd great grandfather" notation, and always used "great great grandfather" myself. Code onward!!