Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2008
    Posts
    6

    Relational DB design

    Hi I'm new to this forum so I hope this is in the correct thread. A friend of mine got me into photographing graves and such. Well the historical society realized what we are doing and started talking to us about creating a book. They have a book from 1986 that has all the cemeteries and headstones listed and where they are and such. They wanted us to make an updated book. I suggested to them that we could create a computer program to do this so it will be easier to update then a book. They loved the idea.

    I know c++ (hobby) pretty well but have very weak database skills but I do understand the idea and started working on it. Right now we have a Cemetery table and Internment(grave) table. The Internment also has a Cemetery ID in it so we know where each headstone is located. But I also want to be able to click on a cemetery and bring up every internment so maybe I need a new table to handle this better. Just to give you the basics of what I have.

    Cemetery table
    -Cemetery ID
    -Cemetery name
    -Township

    Internment table
    -Internment ID
    -Last Name
    -Firstname
    -Middle name
    -Maiden name
    -birthdate
    -deathdate
    -Cemetery ID

    There is more than this but this is the base of it. I wondered if I should instead of having Cemetery ID in the Internment table maybe I should create a new table like below:

    New table
    -Cemetery ID
    -Interment ID

    Then I can do a search by names or by cemetery I think. Help me out with any help u can.

    My second part of the question is this.

    Some headstones only have a death year and not a date or maybe only a birth year instead of a date. We want to be able to search by death dates as well. So how should we do this? Should we have separate fields for Birth Month- Birth Day- Birth Year and same for death month- death-day and death year? Thanks in advance and sorry if this isn't right thread or is a dumb question.

    Jack Adkins

  2. #2
    Join Date
    Nov 2008
    Posts
    6
    I realize this is not only probably the wrong thread but my this whole forum isn't really for database design. Is there any other forums that may suit my question more?

    Jack

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, the 'database concepts and design' forum would be better

    thread moved
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what you have is fine

    the new table with both Cemetery ID and Interment ID would be appropriate for a many-to-many relationship

    far as i know an interment would be located at only one cemetary

    so what you have is fine
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2008
    Posts
    6
    Thank you very much. You made me understand that real well. I'm new to database design so I just wanted to double check with someone who knew. So what you are saying is like if I made a db for books then i would use the third table cause a book could have more than one author. Thanks for clearing that up for me and putting me into the right thread.

    What about the whole birthdate and deathdate problem. Should I use a different thread for month, day, year because sometimes there is only a year listed or should I just use Birthdate and deathdate only and just use 0/0/1958 when the day and month isn't listed or maybe use an X/X/1958? Just wanted to get a second opinion. This field must be searchable too. Thanks again.

    jack

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this is one of the few scenarios where it makes sense to have separate year, month, and day columns -- use SMALLINT, TINYINT, TINYINT, and let them go NULL if you do not know a value

    if you were to use a single column, then in order for it to carry a value which includes 0's or X's or blanks, it would have to be a character column (VARCHAR, or, in Access, TEXT), and then it would be extremely difficult to search on parts of the value

    hence three integer nullable columns
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2008
    Posts
    6
    Ok last post on this just wanted to say thanks alot. I feel like I have learned alot from your posts actually. Even after reading a few articles on database design your posts cleared it up 100%. So I have the database exactly like it should be so that is good so I don't have to reenter records I already have because of change.

    Is there a way to give props or reputation on here so I can give you something?

    Jack

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you already did

    thanks

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2008
    Posts
    6
    So would it be a one to one relationship or one to many? And if one to one how do you do this in access 2003? I figured out how to do a one to many but it seems like I need a one to one relationship to me. Am I wrong on this?

    jack

  10. #10
    Join Date
    Nov 2008
    Posts
    6
    Actually after thinking about this I think it is a 1-many. This stuff can be confusing. Sorry to answer my own question but if I'm wrong let me know.

    jack

Posting Permissions

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