Table design qeustion

[jake]

I'm designing a database for my funeral home. The database will record
all the details for each service. From the data, I want to print out
all my reports needed. The data must reflect the data of that day
(i.e. the cost of a casket in 1992).

I have read and told that I must normalize my tables. If I do I will
have the following:
Vitals
Cemetery
Cremation
Service
Military
Merchandise

with all the above tables in a "one to one" relationship. Several
tables will not be used in every case (i.e. military, cremation).
Is having six tables linked together like this pratical? Or is one
large table a better answer.

 

[Jack MacDonald]

Normalization is the way to go -- one table is almost always a bad
choice.

It's impossible to say whether your six tables listed are correct --
only a careful analysis of the business requirements will determine
that. However, as a general principle, there should be one table for
each "thing" that you are dealing with. Accordingly, I suspect that
you haven't got the right tables yet...

For example, I can probably guess correctly what is a "cemetary" and a
"service", but what is a "vital" or a "military"?

Your requirement for keeping historic costs is an apparent violation
of normalization rules, but is perfectly correct in this situation. I
would expect that your database will have a current price for caskets
(probably as a record in the merchandise table) PLUS you will need to
copy the current price for the casket into a second table when it is
actually used (perhaps in your "service" table). That way, your
database will always retain the price that was in effect when the
casket was sold, plus have a place to store the price if it was to be
sold today.

Finally, one-to-one relationships are generally rare, although they
are useful in special situations. I suspect that you will probably
require one-to-many relations. For example, one cemetary can be used
for many burials.

As another example (and guessing what you meant with the various
tables), is it possible for one service to be for more than one
person? I suspect it is, therefore, it requires a one-to-many
relationship.

If you haven't done so already, I suggest that you visit a store that
stocks computer books, and find one that explains normalization
principles.

Good luck with your project.

I'm designing a database for my funeral home. The database will record
all the details for each service. From the data, I want to print out
all my reports needed. The data must reflect the data of that day
(i.e. the cost of a casket in 1992).

I have read and told that I must normalize my tables. If I do I will
have the following:
Vitals
Cemetery
Cremation
Service
Military
Merchandise

with all the above tables in a "one to one" relationship. Several
tables will not be used in every case (i.e. military, cremation).
Is having six tables linked together like this pratical? Or is one
large table a better answer.

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[jake]

Thanks so much for responding Jack,
I didn't explain what each table was very well, so I will explain.
"Vitals" contains all the background info on the person who has died
(name, address, city, state, ss#, birthdate, etc. - used on every case)
Cemetery - (cemetery - name of cemetery, cemetery_city, Cemetery_State,
etc - used in 75% of the time)
Service - (time, place, type of service, minister, organist - used 100%
of the time)
Military - (served yes or no, branch, war, type of benefits, flag,
rank, headstone - used 30% of the time)
Crematory - (name of crematory, address, used 50% of the time)
Merchandise - (casket, vault, memorial cards, - used 100% of the time)
All of the above tables must store "historic" data - that is reflect
the information of that day and never change.

I have a bunch of Lookup table to supply the current information to my
form (casket costs, vault costs, minister names, ect..)

This is where I run into trouble. I have 6 tables linked in a "one to
one" relationship and make a form from then. Many times you enter data
before you meet with a family and only have a few items such as names,
ss# and address.

If I partially enter a case, and try to go back and edit it - I get
error.

What is the correct answer, 6 tables linked or one big table?
If 6 is right, then should I try to make one form or break them up into
their respective tables?

Thanks for your help,

Jake

 

[Jack MacDonald]

It still sounds to me like you have some normalization errors. This is
how I would approach it:

tblPeople -- contains information about people, whether alive
(minister, organist) or deceased
- PeopleID PrimaryKey - autonumber
- name
- birthdate
- date of death
- SSN
- etc


tblPeopleMilitaryDetails
- PeopleIDfk PrimaryKey - longInteger
- (military service details for the 30% of the deceased who served in
the military
This is the ONLY table that is related One-to-one with another table.
As an alternative, you could omit this table, and include all these
fields in the tblPeople, and leave them empty for the 70% of people
who did not serve in the military. Both approaches are valid.

tblMerchandise
- MerchandiseID - PK - autonumber
- MerchandiseTypeIDfk - longInteger - foreign key to Merchandise type
table
- CurrentPrice
- MerchandiseModel - text
- MerchandiseDetails - memo field - eg. inscription on headstone

tblMerchandiseType
- MerchandiseTypeID - PK - autonumber
- MerchandiseTypeName - eg. headstone, casket

Using this structure allows you the flexibility to add different
merchandise types in the future without altering the database
structure.


tblCemetary
- CemetaryID - PK - autonumber
- CemetaryName
- etc

tblCrematory
- CrematoryID - PK - autonumber
- CrematoryName
- etc

tblService
ServiceID - PK - autonumber
DeceasedPeopleIDfk - longInteger - foreign key to the people table to
record the identity of the person who has ceased
- MinisterPeopleIDfk - longInteger - foreign key to the people table
to record the identity of the minister
- organistPeopleIDfk - as above
- date
- CemetaryIDfk - which cemetary? leave it empty if no cemetary
- CrematoryIDfk - which crematory? leave it empty if no cremation


tblServiceMerchandise
- ServiceMerchandiseID - PK - autonumber
- ServiceIDfk - which service was this merchandise used for?
- MerchandiseIDfk - which merchandise was solde
- Price - what was the selling price of the merchandise

This last table is known as a "junction" table in a many-to-many
relationship between the tblService and the tblMerchandise. It allows
you to designate an arbitrary number of merchandise items to any
service, and to retain the actual selling price that was used at the
time.

This structure presumes that all services are held at one location, or
you do not care where the service was held. Depending on your business
model, that may or may not be valid. You could add another table for
service locations if required.

Thanks so much for responding Jack,
I didn't explain what each table was very well, so I will explain.
"Vitals" contains all the background info on the person who has died
(name, address, city, state, ss#, birthdate, etc. - used on every case)
Cemetery - (cemetery - name of cemetery, cemetery_city, Cemetery_State,
etc - used in 75% of the time)
Service - (time, place, type of service, minister, organist - used 100%
of the time)
Military - (served yes or no, branch, war, type of benefits, flag,
rank, headstone - used 30% of the time)
Crematory - (name of crematory, address, used 50% of the time)
Merchandise - (casket, vault, memorial cards, - used 100% of the time)
All of the above tables must store "historic" data - that is reflect
the information of that day and never change.

I have a bunch of Lookup table to supply the current information to my
form (casket costs, vault costs, minister names, ect..)

This is where I run into trouble. I have 6 tables linked in a "one to
one" relationship and make a form from then. Many times you enter data
before you meet with a family and only have a few items such as names,
ss# and address.

If I partially enter a case, and try to go back and edit it - I get
error.

What is the correct answer, 6 tables linked or one big table?
If 6 is right, then should I try to make one form or break them up into
their respective tables?

Thanks for your help,

Jake

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[jake]

Jack, Wow! thanks you for your quick responses. This gives me something
to do this evening. I be sure to let you know how things work out.
Thanks again, Jake