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.
-- 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 '',
-- 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.