Need advice on db/tables for project

Databases
1

I would like to set up a db for a couple towns (genealogy) and I thought I had it figured out right, and now am not sure. I’m having a little trouble determining the appropriate tables and fields.

There are people, those people have three things happen: they are born, married, or die. Each person has parents. In the case of a marriage act there are two people, and two sets of parents.

I’m not interested in chasing the relationships of each person beyond that (a tree), just creating an online repository of events where people interested in that town can look up a year or a last name and see that their ancestor was born there, and oh that’s who his parents were, and they should go get that film to find out more. Just a finding aid.

The names and surnames in these towns are really repetitive, so I thought I’d create separate tables for name and surname. Then year, town, film, record #, act (b,m,d), and so on.

Where I get really confused is trying to incorporate parents and spouses and stuff.

So, for instance as far as the paper trail goes there is:

Birth
[person] [year] [location] [father’s name] [mother’s name] [record#] [film#]

Marriage
[person] [year] [location] [father’s name] [mother’s name] [spouse’s name] [spouse’s father] [spouse’s mother] [film#] [record#]

Death
[person] [year] [location] [father’s name] [mother’s name] [spouse’s name] [record#] [film#]

What I did was basically boil everything down to an act (bmd) and a person. A person has parents who draw from the same name/surname pools.

surname_id INTEGER AUTO_INCREMENT UNIQUE,
surname VARCHAR(25),
PRIMARY KEY (surname_id)

firstname_id INTEGER AUTO_INCREMENT UNIQUE,
firstname VARCHAR(20),
PRIMARY KEY (firstname_id)

person_id INTEGER AUTO_INCREMENT UNIQUE,
surname_id INTEGER,
firstname_id INTEGER,
mother_surname INTEGER,
mother_firstname INTEGER,
father_surname INTEGER,
father_firstname INTEGER,
PRIMARY KEY (person_id)

where for instance person.mother_surname = surname.surname_id

But… now I’m just confused. Perhaps I didn’t break everything down right after all.

Can anyone provide some guidance?

1221×721 129 KB

2

I wouldn’t use father_firstname and father_lastname (and mother_firstname and mother_lastname) for the people table. Instead I would use father_id and mother_id (or father_person_id and mother_person_id if you think that’s better), that are id’s of other people. That way you can really build the tree. Doing it your if there are two women named Jane Doe and there is a birth of someone who’s mother is Jane Doe, you don’t which of the two it is. If a parent is unknown just set the field to NULL.
The same reasoning goes for spouse_firstname and spouse_lastname in the death table => spouse_id (or spouse_person_id if you will).