 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / January 2004normalize tables
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 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 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.  SignatureSteve Schapel, Microsoft Access MVP Scott wrote: > Greetings, > [quoted text clipped - 89 lines] > Best regards, > Scott B 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 > Scott, > [quoted text clipped - 117 lines] > > Best regards, > > Scott B Scott wrote: > I am I gettng the idea? Yes :-) SignatureSteve Schapel, Microsoft Access MVP Many thanks for all the help. Best Regards, Scott B > Scott wrote: > > > I am I gettng the idea? > > Yes :-) |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.