SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Dec 2006
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    need advice on db/tables for project

    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?


  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,081
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    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).
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •