How to architect an SQL database for genealogical data?

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

Disclaimer: my experience with databases that keep data in tables is old (early 1980s) 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:

• National Genealogical Society, Guidelines to Sharing Information With Others
• Mills, Elizabeth Shown. “Working with Historical Evidence: Genealogical Principles and Standards.” Evidence: A Special Issue of the National Genealogical Society Quarterly 87 (September 1999): 165–84. PDF download available from Elizabeth Shown Mills' site Historic Pathways.
• QuickLesson 11: Identity Problems & the FAN Principle by Elizabeth Shown Mills, from her site Evidence Explained
• Tamura Jones' website Modern Software Experience.
• Falsehoods Programmers Believe About Names by Patrick McKenzie
• Transitioning from person-based genealogy to record-based genealogy?
• Genealogy Standards, 2nd ed. revised (2021)

Answer 4

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.

Answer 5

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.