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:
[person] [year] [location] [father's name] [mother's name] [record#] [film#]
[person] [year] [location] [father's name] [mother's name] [spouse's name] [spouse's father] [spouse's mother] [film#] [record#]
[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,
PRIMARY KEY (surname_id)
firstname_id INTEGER AUTO_INCREMENT UNIQUE,
PRIMARY KEY (firstname_id)
person_id INTEGER AUTO_INCREMENT UNIQUE,
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?