Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2004
    Posts
    1

    Unanswered: Database Design for Census

    Though I have used canned databases, this is the first one that I am attempting to design with PHP/MySQL.

    I've managed to get Dadabik up and running and only after attempting to enter information, do I realize that I haven't a clue how to continue.

    I am setting up a census for genealogy purposes. When I finish entering the first person (the father), I realize that I have no way to add a second person (the mother) without creating a new record.

    I had thought that the database would be named census, the tables would be "Tx1850", "Tx1860", "Tx1870" (since each Texas Census year the captured information changed) and that each family would form a record.

    The problem is that if I design it as I believe that I should, when I finish with person number 1, I have no way to add a 2nd, 3rd ... 9th person. An obviously incorrect way would be to look at what would be the most people ever recorded and have a unique field for each persons entry (ie name1, age1 ... name3, age3, etc.) If I had 10 fields and there were a max. of 9 persons, this could result in each record having 90 fields. Obviously a very low tech approach.

    I don't know how this type of problem is resolved in the real world but it seems that I would need an extra field at the beginning that would indicate how many persons are being enumerated. That allows a computational field into the database and I don't know if thats even allowed.

    Another answer could be that every individual in the census would have a record instead of every family. An extra field could be a unique family ID. When I extract data, I could identify the family Id and several records would be retrieved. I could deal with the iteration in HTML. (This also seems low tech but I like it the best so far).

    Or maybe even a separate table could be setup that would have family id as the unique field. It would use pointer to other table records used by each family member.

    The bottom line is that my strong suit is not database design and I could use the experience of other forum members to identify those solutions that might have hidden problems and suggest solutions that I had not thought of yet.

    Thanks

    Crafty

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    You should understand some Relational Database basics to set this up. Check your library or book store to pick-up a book or two on database design. Most will start with a chapter or two on relational DB concepts and DB normalization explanation.

    You are on the right track with this idea. Have a Family Table, and a Person Table where each person will have a familyID. A one-to-many relationship from the Family to the Person will be used to enable showing Many Persons belonging to a Family.

    Another answer could be that every individual in the census would have a record instead of every family. An extra field could be a unique family ID. When I extract data, I could identify the family Id and several records would be retrieved. I could deal with the iteration in HTML. (This also seems low tech but I like it the best so far).

    Or maybe even a separate table could be setup that would have family id as the unique field. It would use pointer to other table records used by each family member.
    The deeper you get into this the more complexity you will incounter. For example showing relationships of married persons between different families. I find it really helps to draw out a database design to show the entities and relationships. Many programmers think creating a design schema is an unnecessary expense of time, but they can end up with large, hard to maintain DB structures and spend more time revising the program and fixing things that should have been flushed out on paper first. MS Visio Pro is a good tool for this, although the pro version is somewhat expensive. The Visio basic versions do not include the DB Design features.
    ~

    Bill

Posting Permissions

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