0

I have read Bill Karwin's answer on this post and I was amazed on how it solved my basic problem on a Family Tree Genealogy i've been working on (at least on a parent-child relationship or ancestor-descendant level) using MySQL database. The advantages of it compared to Adjacency List and other models makes it a great choice for me given my project. However, as it was discussed, there is no support to connect nodes for sibling or nephew. Furthermore, I wanted to apply such principle to my database project to also support connection for in-laws, nephews, grandnephews, spouse, etc. If possible, I want to extend the Closure Table principle and its queries to support such relationships.

Referring to the image (yellow nodes are spouse): Sample Family Tree Structure

A query with the correct node connections could give answers such as when referring by:

  1. (4) to (5): sibling

  2. (9) to (2): spouse

  3. (3) to (5): nephew/niece

  4. (4) to (3) or (6) to (2): Uncle/Aunt

  5. (1) to (9): child-in-law

  6. (10) to (4): nephew-in-law

and when in such case I referred to a single node, lets say (2), it could give me the list of related nodes and how they relate to each other:

  1. (1) is parent

  2. (5) and (4) are children

  3. (3) is a sibling

  4. (9) is a spouse

  5. (10) is a sibling-in-law

  6. (6) is a nephew/niece

  7. (8) and (7) are grandchildren

There are other scenarios when building family tree genealogies, such as half-siblings (where parent is different, so that you cannot assume that if these two nodes have the same parent); and other relationships like 'my spouse and my sibling's spouse are also sibling-in-law. This also goes that my child is a nephew-in-law of my sibling's spouse).

I'm sure this can be done through the Closure Table, but may not be so simple. Any article or suggestion or queries would be of great help. Thanks! (Maybe someone has already solved this problem a long time ago, I just couldn't find the right resource). :)

Community
  • 1
  • 1
johnsymelle
  • 1
  • 2

1 Answers1

0

Closure tables are useful for directed acyclical graphs (DAG). But genealogy is more complex and DAGs have limited value. I've used Neo4j which supports more complex graphs and enables msec queries of all ancestors or descendants similar to closure tables. It also enables sibling queries:

siblings:

match p=(n:Person{RN:1})-[:father|mother]->(m) match (m)<-[:father|mother*..1]-(s) return distinct n.RN,s.RN,s.fullname

brothers and sisters in law

match p=(n:Person{RN:1})-[:father|mother*..2]->(m) where length(p)=1
match q=(m)<-[:father|mother*..1]-(s) where length(q)=1 
match (s)<-[:husband|wife]-(t) return distinct n.RN,s.RN,s.fullname,t.RN,t.fullname

aunts & uncles

match p=(n:Person{RN:1})-[:father|mother*..2]->(m) where length(p)=2
match q=(m)<-[:father|mother*..1]-(s) where length(q)=1 return distinct n.RN,s.RN,s.fullname

cousins

match p=(n:Person{RN:1})-[:father|mother*..3]->(m) where length(p)=2
match q=(m)<-[:father|mother*..3]-(s) where length(q)=2 return distinct n.RN,s.RN,s.fullname

2nd cousins

match p=(n:Person{RN:1})-[:father|mother*..4]->(m) where length(p)=3
match q=(m)<-[:father|mother*..4]-(s) where length(q)=3 return distinct n.RN,s.RN,s.fullname

and so on.

David A Stumpf
  • 753
  • 5
  • 13