normalize tables

[Microsoft]

Greetings,

I am building an application to track my B&B guest stays and purchases, etc.
I am not a very experienced DB designer although I have a lot of experience
with using Access 2000 after design. I want to find out if my tables are
normalized. I just read Understanding Normalization by Michael J. Hernandez
and I now have a little clearer understanding about the subject, but I am
very unsure about what I can do in regard to linking tables and I would like
some input on my structure.

Here is my table structure and how I plan to link them. I would like to
know also if I need to have a field in each table to link to the other
tables (as I have now) or can I just link each table through the primary
keys?

Customer Table (tblCustomer)
* customerID (primary key)
1 staysID (link to tblStays: customerID)
2 customer_typeID (link to tblLU_customer_type: customer_typeID)
3 gcID (link to tblGift_Certificate: customerID)
4 purchaseID (link to tblItems_Purchased: itemID)
7 referralID (link to tblReferral_Type: referralID)
firstname
mi
lastname
address1
address2
city
state
province
country
postalcode
homephone
workphone
cellphone
fax
e-mail
company
title
customer_notes

Customer Type Table (tblLU_customer_type)
* 2 customer_typeID (primary key) (link to tblCustomer:
customer_typeID
customer_type

Stays Table (tbleStays)
* staysID (primary key)
5 room_stayID (link to tblLU_Rooms: roomsID)
6 stay_paymentID (link to tblLU_Payment_Type: paymentID)
1 customerID (link to tblCustomer: customerID)
room_rate
reserve_date
arrive_date
stay_length
guest_per_room
tax_exempt
cash_sale
weekly_stay

Rooms Table (tblLU_Rooms)
* 5 roomsID (primary key) (link to tblStays: room_stayID)
rooms_description

Items Purchased Table (tblItems_Purchased)
* 4, 8 itemID (primary key) (4 link to tblCustomer: purchaseID) (8 link
to tblLUItems_Type: item_typeID)
amount
date_purchased

Items Type Table (tblLUItems_Type)
* 8 item-typeID (primary key) (link to tblItems_Purchased: itemID)
item_description

Gift Certificate Table (tblGift_Certificate)
* gcID (primary key)
3 customerID (link to tblCustomer: gcID)
gcamount
date_purchased
gcnotes

Payment Type Table (tblLU_Payment_Type)
* 6 paymentID (primary key)
payment_type

Referral Type Table (tblReferral_Type)
* 7 referralID (primary key) (link to tblCustomer: referralID)
referral_type

Any feedback would be greatly appreciated. Thanks in advance for your help.

Best regards,
Scott B

 

[Steve Schapel]

Scott,

The basic concept to follow, is that if there are two entities, both
represented by a table, that are in a one-to-many relationship with each
other, then the table on the "many" side of the relationship needs to
have a field (aften referred to as a Foreign Key) which has data in
common with the Primary Key of the table on the "one" side of the
relationship.

It doesn't work, for example, to have a CustomerID field in the Gift
Certificate table, and also a GCID field in the Customer table. In this
case, I would assume that any given customer could have more than one
gift certificate, but any given gift certificate only belongs to one
customer. In this case, then, the GCID field in the Customers table is
wrong and should be removed. As regards the relationship between
Cuatomers and stays, obviously any given customer can Stay more than
once. It is not clear from your design whether more than one customer
is recorded for each Stay. In either case, the StaysID field in the
customers table is wrong, and should be removed. If the Stay is only
recorded in the name of one customer, then it is correct to leave the
CistomerId field in the Stays table. If the Stay is meant to identify
more than one Cuatomers, you will need to remove the CustomerID field
from the Stays table, and you will need another table to record the
CustomerStays.

Sorry for the incomplete response, but hopefully these comments will
prove useful.

 

[Microsoft]

Steve,

Thanks for the response.

The basic idea for the DB is that there is only one record for each
customer. One customer can stay multiple times, buy multiple gift
certificates, purchase multiple items. So the relationship between
customers and gift certificates, customers and items purchased and customers
and stays are all one to many. Then the other tables are attempts to cut
down the amount of redundant data in the customer, stays, gift certificate
and puchases tables.

If I undersand you correctly, I need to have a foreign key in the stays,
gift certificate and purchases tables because they relate to the one side in
the customer table. But the customer table uses the primary key to relate
to the many side in the other tables?

So graphically,

One Many
- tblCustomers: ID (primary key) = tblGiftCertificate: GCcustomerID (foreign
key [not primary key])
= tblStays: STAYcustomerID
(foreign key [not primary key])
= tblItemsPurchased:
IPcustomerID (foreign key [not primary key])

The subsidiary tables that hold lists of data such as a list of rooms, a
list of items to purchase, type of contact the customer is or the source of
the referral (tblLU_customer_type, tblLU_referral_type, tblLU_rooms,
tblLU_payment_type, tblLU_items_type) are the one side to a one to many
relationship with a main table?

So graphically,

One
Many
- tblLU_customer_type: customer_typeID (primary key) = tblCustomers:
customer_typeID (foreign key [not primary key])
- tblLU_referral_type: referral_typeID (primary key) = tblCustomers:
referral_typeID (foreign key [not primary key])
- tblLU_rooms: roomsID (primary key) = tblStays:
roomsID (foreign key [not primary key])
- tblLU_payment_type: payment_typeID (primary key) = tblStays:
payment_typeID (foreign key [not primary key])
- tblLU_items_type: items_typeID (primary key) =
tblItemsPurchased: items_typeID (foreign key [not primary key])

I am I gettng the idea?

Best regards,
Scott B

 

[Steve Schapel]

I am I gettng the idea?

Yes

 

[Microsoft]

Many thanks for all the help.

Best Regards,
Scott B