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

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

yes, the 'database concepts and design' forum would be better

thread moved

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

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

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

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

you already did

thanks

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

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