Help with design for a Cemetery Database

[Popnorth]

I have been working on this project for about a year now, (spare time only!) and I have read and learned a lot since I began but I still seem to hit the same problem.


I am designing a database for mylocal cemetery. Because it serves asmall area, and because of the nature of the project, people can have severalroles. They can be a Grave Owner, an Undertaker,a Minister, a Clerk, who registers the Burials and eventually end up Deceased. They can also have more than one addressthroughout their lives and several people can live at one address. I recognise these are many to manyrelationships.
I have created a Persons table linked through aPersonsAdresses table to the Addresses Table and have also related the Personstable to the Roles table through the PersonsRoles table.

My problem comes when I try to use the tables (withother tables) to record the Burials. Idon’t know whether I should have a Burials table, or whether it should be aquery. Several people crop up in eachBurial record. There is the Deceased,the Minister, the Undertaker and the Clerk.
If it is a table, then I would need an UndertakersTable, a Clerks table etc. because I can’t repeat “PersonID” in several places,can I?
Please can anyone help me with this problem– I keep thinking that I know how to do it and then I find I can’t!

[June7]

Yes, you can repeat PersonID. If you have a Burials table with a field for each participant to store PersonID, then in a query the Persons table will be included multiple times. The query will call them Persons1, Persons2, etc.

The alternative is to have a BurialInfo table and possibly a related table for the participants, a record for each participant.

[Dal Jeanis]

First, Don't panic. Take a deep breath. You already do know the answer.

Yes, you can use a key in more than one place, even on the same record. But don't worry about that yet.

It sounds like you've done some studying, and most of what you said makes perfect sense. of course, you can't query the data for a burial if you don't have that entity represented in the database yet. After I give you some pointers, I'd like you to go over to Roger Carlson's site at http://www.rogersaccesslibrary.com/forum/topic238.html and look at his tutorial on database design. Do an entity relationship diagram for your application, using his method. Then you'll feel a lot more confident that you're not missing anything.

NOW, MY INITIAL ADVICE.

Think of it this way: Keep your eye on the entities. The nouns. The things.

A person is an identity. You know what a person is.

A plot is a location. It probably will only ever have one person in it, or none. It may have an owner, who may not be the same person who's buried there. There may be other information, like whether or not it is occupied, depending on your design.

A burial is an event. A burial will have only one plot, right? But there could be two ministers, lots of mourners, a funeral director or two, a widow or orphans, and so on. So, the burial record will have a key and a date, and the foreign key of the plot. That makes sense.
However, you wouldn't have a bunch of individual fields on that record that had to be filled in like Clerk and so on. Instead, you have a table that relates burials to people, and includes a field that says HOW the person is related to the burial. (That may be the same as your Role Table) Maybe a particular burial has no ministers, maybe it has three. Designwise, you don't care.

You'll also have a different Rolodex table that represents currently available people-type resources. In other words, if a scheduled minister cancels at the last minute and your cemetery wants to call a new minister, then you query that resource table for people with the minister role, not the other burial table that has all the ministers that have ever been associated with a burial.

It's just common sense, applied to the entities in your application.

Feel better?

You're doing fine. Once you've nailed down your entities, we'll show you how to make your queries less confusing to you, when you're using the same table for multiple different reasons in the same query. It's called an alias, and it's very easy.

Okay, go visit Roger, http://www.rogersaccesslibrary.com/forum/topic238.html, and nail down your design.

[Popnorth]

Thanks to both of you. I have been working on a model but although I have read some of Roger's site, I hadn't found that part. I'll get reading!