{"id":145,"date":"2010-07-30T22:41:03","date_gmt":"2010-07-30T17:41:03","guid":{"rendered":"http:\/\/www.papaworx.com\/Blog\/?p=145"},"modified":"2011-08-27T20:57:05","modified_gmt":"2011-08-27T15:57:05","slug":"a-genealogical-data-model","status":"publish","type":"post","link":"https:\/\/www.papaworx.com\/Blog\/2010\/07\/30\/a-genealogical-data-model\/","title":{"rendered":"A Genealogical Data Model"},"content":{"rendered":"

Sooner or later, most engaged genealogists outgrow their paper napkin diagrams, typed charts and even filing cards as\u00a0preferred method of record keeping; they move on to some form of genealogical software. In my case, that step happened relatively early. I did a survey of freeware and\u00a0commercially available software and was not impressed. No package offered all the features I desired, and none had adequate extension capabilities. Obviously, I had to design my own. But to tell the truth, necessity wasn’t the only reason for my DIY choice. Designing software gives me as much pleasure as watching porn movies or propagating rare orchids may give to others.<\/p>\n

The die was cast. I would write my own. But what about context? I wanted the program for myself, to meet my needs, and I wasn’t going to commercialize it. It had to run on a PC platform; after all, real men don’t use Macs! My choice for programming language was between C++ and Visual Basic. I am too lazy to keep track of pointers. So I was pretty much limited to VB6 for the first version.<\/p>\n

The next big issue was the basic data\u00a0model for genealogical records. Obviously, my research led me to GEDCOM<\/a>, version 5.5.2 . GEDCOM goes back to the 1980s, when it was developed by the LDS church for public ancestry files. It has evolved since and become the de facto standard for serialized genealogical records.\u00a0 For several years there was talk\u00a0of GEDCOM XLM, but it never made a mark; and why should it? GEDCOM 5.5.2 is perfect – perfect for serializing genealogic records!<\/p>\n

And\u00a0there’s the rub. Efficient software design involving large data sets requires a relational database<\/a>. Yes, GEDCOM can be forced into the Procrustes bed of relational tables. But basing a genealogical\u00a0program entirely on the GEDCOM structure means sacrificing execution time – big time. Drawing a kinship tree\u00a0iterating through the basic GEDCOM\u00a0model is like sucking treacle through a straw. On the other hand, taking a steam roller to GEDCOM to force the data into a flat table is no answer either. Resulting rows would contain hundreds of columns, most of them empty.<\/p>\n

This conundrum gave me quite a few sleepless nights. But my epiphany arrived just in time: I would keep the information in a GEDCOM structure\u00a0in the form of\u00a0a simple, three column table corresponding to the three columns of a GEDCOM record: “Level”, “Tag” and “Value”. Actually, I lied. There are four more columns to make things work: “Node”, “Parent”, “Root” and “Cheat”. Each\u00a0record has a unique index number called “Node”. To maintain the hierarchical structure of GEDCOM, any record can act as a parent to child records. Child records carry the node number of their parent record in\u00a0their parent field. In the context of genealogy, this\u00a0terminology can be confusing. ‘Parent’ and ‘Child’ refer only to a relation between records, not people. Any record with level zero is considered a root record. Its value goes into the “Root” field of itself and all its descendants. That leaves us with the “Cheat” field. It is a boolean, usually ‘false’. I wanted to leave myself the option of cheating, i.e. to use non LDS-sanctioned tags. But I wanted to cheat honestly by\u00a0signalling where a tag wasn’t ‘kosher’\u00a0with a ‘Cheat’ value of ‘true’!<\/p>\n

The last ingredient to the data model are the two index tables, one for individuals, the other for families. But having the same data in more than one table of a relational database would probably cause Edgar Codd\u00a0<\/a>to turn in his grave. In fact, you can’t even call the database relational. OK, so I call it pseudo-relational. There is a\u00a0price, though, I have to pay for this act of transgression: every transaction involving writing to a root record or changing a family composition\u00a0must pass through a compulsory\u00a0integrity check. One can also wrap this operation into an SQL transaction.\u00a0But it is a small price to pay.<\/p>\n

The program worked like a charm. It was robust, fast and could perform all the functions I wanted. Since then I have written a second version in C#. It is even faster and easier to maintain and extend. It runs on all our networked computers. My wife and I both use it simultaneously. The database runs in an MS-SQL-Express server on one of our machines.\u00a0When we travel, we\u00a0create a temporary copy of the database on the laptop.
\n<\/p>\n

Bragging rights<\/h3>\n

\"\"<\/a>Allow me to brag a little about our private genealogical database manager. When Chainpro comes up, it shows the individual page of the default person. The default person can be selected and stored. It shows not only the vital data but gives alo the closest relatives in appropriate drop downs. When one of these is selected, the page switches to this person. There is a large text box for biographical data. Any number of documents, be they pictures, pdf files, videos or URLs can be attached and watched.

\n\"\"<\/a>On each page there\u00a0are two\u00a0‘Family’ buttons, one\u00a0each for the partner displayed and the parents. It provides information on the nuclear family in question, including dates and places of engagement, marriage etc. It also shows the children in this nuclear family.<\/p>\n

\"\"<\/a>\u00a0Besides the individual page there is also a kinship page available that shows an ascendant\u00a0or descendant family tree. Nodes are expandable, either individually or globally, to show as much of the tree as desired.<\/p>\n

\"\"ChainPro also has extensive ‘Find’ capability to find a person by menu assisted name searches, by dates or place or by involvement in the holocaust.<\/p>\n

As I said, we like the program, we use it extensively, and it is not for sale or distribution. I am simply showing it here to brag and to illustrate the power inherent in the data model described.<\/p>\n","protected":false},"excerpt":{"rendered":"

A data model is described for genealogical software which combines the optimal features of the GEDCOM data structurem with those of a relational data base. The model is illustrated with a proprietary genealogical database manager. Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_s2mail":"yes","footnotes":""},"categories":[4,3],"tags":[],"_links":{"self":[{"href":"https:\/\/www.papaworx.com\/Blog\/wp-json\/wp\/v2\/posts\/145"}],"collection":[{"href":"https:\/\/www.papaworx.com\/Blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.papaworx.com\/Blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.papaworx.com\/Blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.papaworx.com\/Blog\/wp-json\/wp\/v2\/comments?post=145"}],"version-history":[{"count":37,"href":"https:\/\/www.papaworx.com\/Blog\/wp-json\/wp\/v2\/posts\/145\/revisions"}],"predecessor-version":[{"id":152,"href":"https:\/\/www.papaworx.com\/Blog\/wp-json\/wp\/v2\/posts\/145\/revisions\/152"}],"wp:attachment":[{"href":"https:\/\/www.papaworx.com\/Blog\/wp-json\/wp\/v2\/media?parent=145"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.papaworx.com\/Blog\/wp-json\/wp\/v2\/categories?post=145"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.papaworx.com\/Blog\/wp-json\/wp\/v2\/tags?post=145"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}