Solved

how to fill in a family tree

Posted on 2014-01-08
6
485 Views
Last Modified: 2014-01-31
I work on an Oracle database for a school where we store extensive cross-reference information between constituents. We record parent, child, sibling, grandparent, grandchild, aunt/uncle, niece/nephew, cousin, and many other relationships.

I've been asked to help our data entry personnel fill in the blanks that inevitably form when building some of our constituents' family trees.

Does anyone have an algorithm that can analyze existing, known relationships and discover missing links? I *don't* need to graphically display anything; I only need a simple list as output of the relations and corresponding relationships that are missing.

Our data is stored as very simple records with the IDs of each person and the relationship between them. For readability, I'll use names instead of numbers in an example:

Nancy - parent - John ("Nancy is the parent of John")
John - child - Nancy
John - spouse - Sue
Sue - spouse - John
John - parent - Mary
Mary  - child - John
Sue - parent - Joe
Joe - child - Sue
Bill - sibling - Nancy
Nancy - sibling - Bill

If the above is all that was entered into the database, I am looking for a programmatic way to list all the implied relationships:

Sue - parent - Mary
Mary - child - Sue
John - parent - Joe
Joe - child - John
Nancy - grandparent - Joe
Joe - grandchild - Nancy
Nancy - grandparent - Mary
Mary - grandchild - Nancy
Joe - sibling - Mary
Mary - sibling - Joe
Bill - aunt/uncle - John
John - niece/nephew - Bill
Nancy - parent-in-law - Sue
Sue - child-in-law - Nancy

Any code or guidance would be a big help!
0
Comment
Question by:prinprog
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39765770
Just looking at your first line item as an example... I think you are making an incorrect assumption.  -- (based on a lot of genealogy of I've worked on)

you had as implied : Sue - Parent - Mary.

existing :

John - spouse - Sue
John - parent - Mary

If John had Mary by a previous marriage, then Sue would not necessarily be a parent of Mary.  Or is that possibility something you would want to ignore?  or do you consider that  Mother=StepMother?
0
 

Author Comment

by:prinprog
ID: 39765817
Thanks for the observation. I'm choosing to ignore that aspect, and is why we probably don't want to automate the creation of these relationships, but rather just report them. They will probably require some research to see exactly what the situation is. But at least to get them started, I want to be able to present a best guess. Perhaps version 2 can attempt the complexity of half- and step-siblings.
0
 
LVL 26

Expert Comment

by:pony10us
ID: 39765844
"Perhaps version 2 can attempt the complexity of half- and step-siblings. " 

Don't forget about adoptions in V2.   :)
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 19

Accepted Solution

by:
Ken Butters earned 500 total points
ID: 39765937
What you are asking for is not trivial.

You would need a relationship calculator to determine all the possible relationships.

In order to do that, you could (1) write your own or (2) use existing genealogy software.

Here is an example of a relationship calculator:
http://www.codeproject.com/Articles/30315/Tree-Relationship-Calculator

Here is a page that has 4 different relationship calculators:
http://www.myrelative.com/

If you use off the shelf genealogy software, most of those can build relationship reports.
0
 

Author Comment

by:prinprog
ID: 39766144
Thanks pony10us.  :-)  In our system, for our purposes, we would probably code that as a full child and not make the distinction. For that matter, I'm not actually sure how we track half-siblings, though I know we do code step- relationships.

Ken, thank you for the links -- and for confirming that this is not trivial! I've been trying to tell my clients that while it's easy to think through, it's not easy to code. I'll have a look at those links.

I'm still open to more suggestions and observations if anyone thinks of other complexities or ideas.

Thanks for the great feedback so far!
0
 
LVL 15

Expert Comment

by:JimFive
ID: 39824305
I think that I would look at things differently.  Instead of building a result based on what you have, build a result based on what is missing.
1.  Missing reciprocal relationship (John-Parent-Mary requires Mary-Child-John)
2.  Missing relationship:  (Mary-Parent-Missing)

Then, if you want to go further you can attempt to match Missing based on assumed relationships (Mary-Sibling-Parent -> Mary-PotentialParent, Mary-Parent-Spouse -> Mary-PotentialParent)  Recognizing that this step is speculative.

Beware of False transitivity, My cousin's cousin is not necessarily my cousin.
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question