2

I want to make all my genealogical data available to the rest of my family by making the data accessible on a website.

As a software engineer, my go-to solution for this is a simple web application with an underlying SQL database.

However, I'm not certain what to record in this database or how to record it. I have some ideas, but I'm assuming this is a "solved problem" - surely someone must have figured this out before me.

Currently I've written this definition of a database of persons, with a row for every individual in the genealogical data:

-- Directory over people.
create table persons (
    id serial primary key,

    -- This is the person's most common (full) name or latest name.
    -- Another table could be added to contain historical and more accurate names.
    name text not null,

    -- Parents may be null because they might be unknown.
    -- Biological father.
    father_id integer references persons,
    -- Biological mother.
    mother_id integer references persons,

    -- The day the person was born.
    birth date not null,
    -- The day the person died.
    -- Null indicate that the person is still alive.
    death date,

    -- The description is just a free-form dump of information.
    -- Could be written by admins or by the person themselves.
    description text not null default '',
    check (
        -- Name must be non-empty.
        name != ''
        -- Parents can't be the same person.
        and father_id != mother_id
        -- You can't die before your birthdate.
        and birth <= death
    )
);

In this table, I've recorded the birth and death dates as columns in the persons table. However, I'm wondering if, for example, it's better to use an event-based method? Like having an events table that would then have a birth event and a death event with associated dates.

There is also the question of how I indicate sources for the information in the database. For instance, how would I indicate the source for the name of a person or their parents?

I realize this is a somewhat broad question, but there is really not a lot of material out there when it comes to architecting an SQL database for genealogical data. Are there any guidelines in this area or perhaps does anyone have any advice?

I've seen GEDCOM, but personally I find that format pretty horrible to work with and I would hope that there is something with better practices than GEDCOM out there.

9
  • Have you reviewed genealogy.stackexchange.com/q/5525/19? I suspect the open source code of answers there may let you see the SQL that underpins some of them.
    – PolyGeo
    May 13, 2023 at 6:53
  • @Victor I have done what you are asking about for a Wordpress plugin. However, I am away from home for a few days so unable to answer fully at the moment. I will do you a detailed reply in a couple of days.
    – Colin
    May 13, 2023 at 7:59
  • @PolyGeo I have. Gramps seems to have an SQL backend, but that's just one of many backends it looks like. And I can't find any actual concrete SQL code in their repository, so it's very difficult to see how they use SQL to save genealogical data. The rest of them either aren't open source or don't seem to be using SQL at all, as far as I can tell at least. Do point me somewhere if you find one that does use SQL and clearly shows how the data is organized. May 13, 2023 at 10:17
  • @Colin thanks, looking forward to your reply :) May 13, 2023 at 10:18
  • If the goal is to put your data on a website, some genealogy software will do this for you. This was created with PAF (now obsolete, so probably not a good choice) mattman944.com/genweb Gramps will make webpages also, but I haven't tried it, there are probably others.
    – Mattman944
    May 14, 2023 at 16:43

5 Answers 5

2

Disclaimer: my experience with databases that keep data in tables is older then SQL but I understand basics (e.g. the virtue of normalizing). Rather than getting into the details of SQL or any other specific database, let's consider some larger design issues.

First of all, you haven't discussed what kind of data your database will contain. Do you want a place for your conclusional data, as most genealogy software records, or do you also need a way to track the data about the what a prior question called the multiple maybes?

Secondly, I would suggest that you step back for a moment and consider how you want to address the links between the information in the database and its origin.

You wrote:

There is also the question of how I indicate sources for the information in the database. For instance, how would I indicate the source for the name of a person or their parents?

I don't know how finely-grained you want to be in recording your sources, or whether you follow the older style of evidence assessment / analysis which is still used in Britain, or Elizabeth Shown Mills' newer style, summarized in her Evidence Analysis Process Map, For the sake of this answer, I will refer to the source as a container which holds information as Mills presents in her 3 x 3.

When building source citations, some programs have a two-level system of recording high-level information about a source (e.g. 1950 US Federal Census) and then copying that information when it is required to build a citation (e,g, the specific census household where a person appears).

Another method is to treat a specific media object (e.g. a single page of the census) as a separate 'source' and to have a flatter file with have many more 'sources'. This is referred to by users of Family Historian as "the lumper/splitter problem".

Whichever way you resolve the problem under the hood, assign a key number to the citation and associate it with the information about each fact or event. You need to be able to determine where information comes from. If you do not, and you discover later that someone has fabricated data (Gustav Anjou, ChatGPT, and the like), you won't be able to pull that data back out of your database.

Another thing to consider is whether or not you want to have hooks for a graph database (see GRAPHical Family Trees from the findmypast blog). Table-based databases are good for tabular data like census returns and other standardized forms. For narratives and other free-form information, a graph database may be better suited. Sources used for genealogy and family history often contain information about relations outside the immediate family (grandchildren, nieces and nephews, cousins, etc.) and other members of the FAN (extended family and friends, associates and neighbors). It is much easier to record this data in a graph database, where the underlying concepts are nodes (people, businesses, fraternal organizations, etc.) and relationships.

You also wrote: "[S]urely someone must have figured this out before me."

Reviewing prior work such as the data models created by FHISO (The Family History Information Standards Organisation), BetterGEDCOM, Tony Proctor's STEMMA Project, and Tom Wetmore's Lifelines might help in bringing to light other design problems you haven't considered yet. If you haven't already, you can search Genealogy Stack Exchange for related design questions.

You have stated a purpose for your database: "to make all my genealogical data available to the rest of my family by making the data accessible on a website" so perhaps digging into the data models is overkill. It might be simpler to consider a review of all the programs that currently display data from a GEDCOM and then assess how the existing software doesn't meet your needs.

If I were designing a new database, I would want it to be source-centric rather than people-centric. I have different requirements than most people do (summarized briefly: if I were in charge of Ancestry, I would index every single piece of data in a historical record, not just the obvious name, date, etc.).

I could pontificate all day on the deficiencies of GEDCOM that make me cry "Didn't these guys ever do any genealogy before they designed this program?" but I'll suggest some things I would like to fix if I were designing my own database from the ground up. Features of my ideal database would include

  • Tools to make sure that private information stays private and is not shared on the Internet
  • Features to take into account that not all cultures have a gender binary (see also https://genealogy.stackexchange.com/a/15849/1006)
  • The default setting for the parent-child relationship should be 'undetermined' rather than 'biological'. Consider how many people only discover they are not the biological offspring of both their parents after they take a DNA test.
  • Better handling of names to account for different cultures which may carry multiple surnames or have none at all; multiple married names of women, name changes (not at Ellis Island!) by immigrants, and the simple fact that two people with the same surname sometimes marry each other.
  • Error checking of dates on data entry or validation should take into account that some events associated with a person's life (probate, obituaries) routinely happen after a person's death

If your current data is held in a GEDCOM,consider using tools like Family Tree Analyzer to find elements of your family's history that you want to highlight.

Resources:

2
  • 1
    Thanks for the detailed response. I think for my purposes, just holding conclusions and references to sources of those conclusions will be enough. "If I were designing a new database, I would want it to be source-centric rather than people-centric." That sounds good, but how would you achieve this concretely? I plan to ensure that information can only be seen if you're logged in, for privacy's sake. Currently I'm storing names as a single string, as I think splitting in given/surnames is ultimately fruitless as it has too much complexity. Jun 18, 2023 at 21:34
  • @VictorNordamSuadicani The source-centric vs. people-centric issue is discussed elsewhere on the site and is beyond the scope of your question. I only brought it up as an example of how the task we want to do shapes our thoughts while designing and sometimes blocks us from seeing more flexible designs. In other words, GEDCOM's design reveals some of the blinkers the designers had on, having focused too tightly on their own family structures and not those of other cultures.
    – Jan Murphy
    Jun 20, 2023 at 2:41
1

My solution


I've been taking on this project myself as well, albeit I am staying away from an SQL database. Though I am very familiar, I don't have SQL installed on my personal computers and I felt like it was a bit overkill anyway.

I have instead opted to make my program in JavaScript and store my data in JSON files.

Each person has their own JSON object with certain properties.

{
    "id": 148,
    "name": "Maurice Clark",
    "born": "Apr 15 1967",
    "sex": "M",
    "died": "Mar 28 2006",
    "father": 8,
    "mother": 146,
    "siblings": [147,149,150],
    "spouse": 182
}

As you can see, each person has a unique id, their full name, the date they were born, the date they died, who their father/mother was, who their siblings are, and who their spouse is, as well as male vs female.

So far this has treated my fairly well. I am able to link people together with each other based on this information.

Now for my purposes I also have an interest in keeping track of other things, such as where the person is buried and the hyperlink to their findagrave URL. But I have a separate JSON object (separate table if SQL) to keep track of this as I consider it "extra data".

What could be done better


In SQL the siblings property might need to be a separate table to accomplish the one to many relationship going on here.

You may also wish to treat spouse as a one to many "spouses" if you wish to keep trach of those family members. For my purposes I only track the spouse with which the person had the children who are directly related, I don't bother tracking half- relationships. But you might wish to, and I would again suggest a separate table like siblings to accomplish the one to many.

I also don't store the relationship value (father, mother, 1st cousin, uncle, grandaunt, etc.), I have functions that figure that out depending on the "starting" person. Because my dad is your 1st cousin 1x removed, or what have you.

Perhaps you might want to separate out the name instead of one field handling the full name, having a first name, middle name, last name, and maiden name field. You could also have an aliases table that stores additional names for people. For example we have a William that everyone knew as Bud. For me, I just put his name as William "Bud", but that might not be as elegant as what you want.

Missing Dates


Also something that I would like to point out is where you mention this

-- The day the person was born.
    birth date not null,
    -- The day the person died.
    -- Null indicate that the person is still alive.
    death date,

Learn from my mistake and realize that that is a bad idea. A missing death date does not indicate the person is still living. Since starting I have run into quite a few people who are definitely dead, but I cannot find any source for the date of death anywhere. You should have a separate true/false field to handle living/deceased.

For that same reason you should not make birth date un-nullable. There may truly be no known birth date for the person, and that is okay, it happens, and you don't want to fabricate a date for it.

Conclusion


That's about what I have to offer for advice. Add/remove/modify the data structure I've presented to meet your own needs. Perhaps you want to add a burial field to track which cemetery the person is in, cremated, body was donated to medical science, etc. Maybe you find that keeping track of siblings is a bad idea as you could instead to always run a function to find people with the same mother/father every time you need that information (if using SQL you could create a view to handle this).

A note on GEDCOM


Also, if you have family members who have been filling out their family tree on Ancestry it is really simple for them to export their tree to a GEDCOM file and send that to you. Personally, I went the full mile and made a GEDCOM->JSON program that very very roughly accomplishes what I need. GEDCOM files are terrible, I agree with you there, but they aren't impossible to understand. Each "block" will start with either an individual ID or a family ID, and each line will start with a number, 0 starts a new block, 1 is a first-level child property of 0, 2 is a child of a 1, 3 is a child of a 2, etc. Think of the numbers as how many tab indents the line is.

1

This is how I have structured my tables

Names table - Stores the given and surname, gender, living, reference, (date of birth and date of death only needed for my application may not be needed by you) and Note.

Facts table - fact reference, type, detail, date, place and note

Marriage table - marriage type, date, place and family reference

Family table - family reference, role, individual reference.

All tables have an auto increment id.

Hope this helps. Any more questions post back.

++++++++ This design is based on accepting a Gedcom file as input and putting the data into a MySQL database.

The facts table is the largest as it contains all the facts about the individual. Don't forget many facts reference more than one person and so you store the fact once and link it to many individuals using the fact id.

Likewise families can have many components a child in one family can / is the parent in another family. Don't forget adoptions and multiple marriages.

This design helps with data normalisation and reduces the amount of data duplication in the database tables.

4
  • Thanks, that's useful. A "Facts" table sounds awfully non-specific. What kinda types go in that? What about facts that don't necessarily have a place? What is the purpose of tracking families? Can't families be derived from existing data (assuming a family is just parents + kids)? May 17, 2023 at 21:52
  • Desktop software products use the fact types defined in the GEDCOM spec, plus usually each have a few of their own custom fact types. So, the obvious vital stats facts like Name, Sex, Birth, Baptism/Christening, Marriage, Death Burial, and also things like Residence, Graduation, Military, Probate, etc. In general facts have fields for date, place, and description, although for some, name & place aren't generally used (e.g. Name & Sex, although I could see that changing). And yes, a family is usually just parents & kids, but how do you associate them without a family ID?
    – cleaverkin
    May 18, 2023 at 23:41
  • The way I've thought about biological relations is just to have each person have a mother ID and a father ID that refers to the person's parents. Then there is no need to track "families" as a concept separately. May 20, 2023 at 16:23
  • Without a family how do you track siblings? What about adopted children how do you store birth mother / father vs current mother / father? These are things that you need to think about.
    – Colin
    Jun 20, 2023 at 6:28
1

Dates of birth and death, as well as dates of marriages, divorces and other events, can be incomplete. For example, year of birth is known, but month and day aren't. You need to take it into consideration.

0

Have you folks looked at: SQLiteToolsForRootsMagic.com

It's a site for users of the commercial RootsMagic software. It includes a full schema and many SQl useful scripts.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.