Family Tree SQL production

[Lightwave]

Dear All

I have a single table of a list of persons that is referenced to itself for the father and mother field.

So lets say its called t001 and has a primary key of PKID structure is similar to the following where pkidt001mother and pkidt002father is referenced to itself.

pkid
firstname
surname
pkidt001mother
pkidt001father

Anyone got any pointers on how to write the SQL that could pick up collections of related individuals...?

Just playing about with this structure but at the moment I really like it.

 

[Lightwave]

Hang on I see there is an actual relevant thread in the Modules and VBA posted yesterday of all times... I'll have a look at that.

 

[MajP]

Yep, if you have questions I am the Dude. However touch base with @The_Doc_Man who has done extensive work in this area.

 

[The_Doc_Man]

Thanks for the ref, MajP.

Lightwave, all I can say is this: It ain't easy. What you are describing is similar though not identical to GEDCOM data file format (GEneological Data COMmunication format). First step? Learn LOTS about recursion because a single SQL statement ain't gonna cut it. I'm going to presume that at least some people from that data set of yours are somehow related to each other BUT not all, and that you might have some cousins or siblings in the mix. And depending on its source, some total strangers.

In my case, I have a recursive routine that I can use to deal with relationships. It is probably a bit overblown, but let's just say it's a "tale that grew in the telling." To make the collection that I want, I have a field in that table that says "selected." Then I erase the selected field before I do the recursive analysis that marks the places I visit during recursion. THEN I can use SQL to put together what information I have into a report of some kind.

The trick is, of course, to watch for those dotted lines. There is a person in my extended family for whom there is a father by marriage, a biological father, and a father by adoption. The best family tree builder I've got still doesn't like that little knotty problem.

What I have is specific for GEDCOM files so I don't know if you can use it. But I'm happy to talk about specifics if you wish.

 

[MajP]

The trick is, of course, to watch for those dotted lines. There is a person in my extended family for whom there is a father by marriage, a biological father, and a father by adoption. The best family tree builder I've got still doesn't like that little knotty problem.

How do you tackle the non biological stuff? Without having done anything with more than 2 parents I would think my structure to handle that would be a one to many.

tblParentalRelations
--- childID_FK
--- ParentID_FK
--- RelationType (bilogical mother, biological Father, Step Mother, Step Father, Adopted Mother, Adopted Father, Surogate Mother)

That way you can handle multiple step parents and adoptions.

 

[The_Doc_Man]

Non-biological stuff? Pretty much like you showed - a relation type field that is more than just mother or father. And I of course make it a true junction table so that not only can I do ancestors, I can do siblings and descendants. I.e. it goes both ways. Anyone who wants to can look up a GEDCOM file. Here is what I produce with my DB so far:

The Ancestor diagram is three generations to a page, parents only. The Descendant diagram is mother, father, and children for one generation to a page. The little arrows to the left or right, pointing to a number, are off-page references. The Family Data display includes most of what I know about anyone who was included during the process of creating either of the two graphic diagrams.

By the way, the actual images for the two graphic items are done in Excel with some creative formatting. The family report is exported to Word. Both the Excel and Word files are then saved a second time as PDF since I share these things with my family and not all of them use Office. But a PDF goes darned near anywhere.

The section I showed for the family details is something I did for my mother-in-law before she passed away. I was able to show her that she had ancestors from Acadia, Nova Scotia from the time of the Acadian Diaspora. This is the Cajun equivalent of coming over on the Mayflower and is documentation that she was from one of the oldest families in south Louisiana. Made that old lady really happy to know that. She had a chance to brag about it a little bit at the nursing home.

 

[MajP]

What format is the output? Is this the excel thing you were working?

 

[The_Doc_Man]

There are three outputs. The two diagrams are indeed Excel spreadsheets with all lines turned off and with some other "diddling" of column widths plus insertion of special characters (the arrows mostly). The text segment is just a report of certain data found in passing during the generation of the two Excel presentations.

 

[MajP]

Did you look at Visio since it handles complex org charts? Might have cost some money, but may have had more capability. I imagine in excel you had to build everything yourself.

 

[The_Doc_Man]

Actually, I futzed around with an Excel feature called Smart Art that has the ability to draw some impressive diagrams, but the thing is incredibly close to totally useless for what I was attempting. Using Access VBA to control an Excel object to in turn control a SmartArt object qualified as damned near impossible. The SmartArt object done as a template reconfigured dynamically every time I touched it and I couldn't make it draw the picture I wanted programmatically.

I didn't have Visio. Since I started the project from home after I retired, I would have had to buy a copy.

Yes, I had to build everything myself from the ground up. Including the general text parser that decomposes the GEDCOM files. Took me a while to figure out those rules too. When I saw how to do those ancestor and descendant diagrams in Excel, I realized it could be good enough for what I wanted to do. My cousins seem to like it and I'm not picky. Sometimes "simple" gets the point across.

 

[gemma-the-husky]

@OP

when you can find code, the recursion will look like this

descendants.
starting from any given node (me), you find all children (ie, people whose mother or father is me)
from each of those children you do the same thing - that's the recursion.

ancestors
starting from any given node (me), you find all parents (which is your foreign key)
from each of those parents you do the same thing - that's the recursion.

relations, siblings cousins, etc
this is where it gets tricky.
siblings are other children of your parents
uncles and aunts are siblings of your parents
cousins are children of your aunts and uncles.

so to find these, you have to be able to find siblings. These are people who share your parents, but aren't you.

The recursion process is similar, but for this one you have to be careful to never visit persons more than once, as you will most likely encounter the same people from multiple traversals, which is what the doc man was getting at when discussing "marking" people visited in the traversal.

so this sort of aircode should do the job for the easier relationships. I will leave you to think about including the complexity of iterating sibling relationships.

sub getparent(p as person, level as integer)
'iterates the whole tree to find all my ancestors
       outputpersondetails(p, level)
      with p
              if p.father <> null then getparents(p.father, level+1)
             if p.mother <> null then getparents(p.mother, level+1)     
     end with
end sub

sub startparentprocess
'set me as the root, and then start the process.
       getparents(rootperson,0)
end sub

sub getchildren(p as person, level as integer)
'iterates the whole tree to find all my children
       outputdetails(p, level)
      with p
             generate a query to find anyone whose father or mother is "p"
             for each child in this query
                    getchildren(child,level-1)
            next
     end with
end sub

sub startchildprocess
'set me as the root, and then start the process.
       getchildren(rootperson,0)
end sub

 

[The_Doc_Man]

The recursion process is similar, but for this one you have to be careful to never visit persons more than once, as you will most likely encounter the same people from multiple traversals, which is what the doc man was getting at when discussing "marking" people visited in the traversal.

Absolutely true, Dave. And it gets worse because of the problem of "dotted lines." The way that I do this is similar to the way Ancestry.COM does it. You don't always care about people. You care about "families" which consists of a father, a mother, and children. BUT everything has two-way junction table setup so that you CAN go "back in time" to find a patrilineal ancestor OR you can see a list of first-borns from a particular person. Took a while to get that even slightly close to right, and to be honest I make NO guarantees.

As a result of those dotted lines, you sometimes DO have to revisit the same person twice when that visit is with a new partner, as might happen with a divorce and subsequent new marriage. So the combination also requires special marking.