If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Normalisation and Relationships Problem
Hi
I'm very new to Access databases and I'm trying to set up a system for a DVD rental shop. At the moment I have my tables set up as below, but I've been told that this is not normalised and won't work properly. Apparently I need to add another table. I'm also not sure what type of relationships I should have. tblCustomer Customer ID PK Title Surname Forename Address Town Postcode Contact Number Film Genre Payment Method Join Date Paid Membership? tblRental Rental Number PK Customer ID FK DVD ID FK Nights Rented Date Rented Date of Return Cost Per Night Normalisation tblDVD DVD ID PK Film Genre DVD Title Main Actor Film Length Rating 5 Star Rating Any help would be much appreciated. Thanks |
#2
|
|||
|
|||
Normalisation and Relationships Problem
Well, it depends a lot on your business rules. But from what I can see,
you've got a Many-To-Many relationship between Customer and DVD. That is: Each Customer can Rent One Or More DVDs Each DVD can be Rented by One Or More Customers. The Rental table you've got creates the Junction (or Linking) table that is required to create a M:M relationship in a relational database. So I guess what I'm saying is that those relationships are correct. But in order to determine if it's properly normalized, I have to ask some questions. 1) What is "Title" in the Customer table. DVD title? If so, it's in the wrong table. Same with "Film Genre". 2) Does the customer have only 1 contact number? If so, that's all right. But if the customer can have multiple contact numbers, then you should have another table for it in a One-To-Many relationship. 3) What is "Nights Rented" for? You don't need to store that number if you've got the Date Rented and Date Returned values. You can calculate it. 4) Film Gen Is a DVD categorized by a single genre? Or can it have more that one categorization? If the latter, like contact number, it needs it's own table. 5) What is your "Normalisation" field in Rentals? 6) Presumably, Rating includes 5 Star Rating, so the latter is unnecessary. 7) I would also contend that you need several Look Up tables. These would be used to provide values to the main tables: Payment Method, Film Genre, Rating, Cost table, and depending on how fancy you want to get, Town and Postcode (although I generally don't bother). Good luck with your class. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "newbie2db66" u47694@uwe wrote in message news:8d7b071ff443e@uwe... Hi I'm very new to Access databases and I'm trying to set up a system for a DVD rental shop. At the moment I have my tables set up as below, but I've been told that this is not normalised and won't work properly. Apparently I need to add another table. I'm also not sure what type of relationships I should have. tblCustomer Customer ID PK Title Surname Forename Address Town Postcode Contact Number Film Genre Payment Method Join Date Paid Membership? tblRental Rental Number PK Customer ID FK DVD ID FK Nights Rented Date Rented Date of Return Cost Per Night Normalisation tblDVD DVD ID PK Film Genre DVD Title Main Actor Film Length Rating 5 Star Rating Any help would be much appreciated. Thanks |
#3
|
|||
|
|||
Normalisation and Relationships Problem
In addition to what Roger said, here is something else you should
consider. Presumably, you would allow a customer to rent more than one DVD at a time. With your junction table as it is now, you will have to initiate a new rental transaction (invoice) for each DVD a customer rents. It would be better to have a table for rental line items related to the junction table (tblRentals) via RentalID. You should carefully consider how you charge for rentals also. Most places have different prices for different movies depending on whether it is a new release, an older movie, a rental special, etc. If this applies to your business, then you would probably want a table for categories, and the rental cost of each category would go in that table. In this case you would probably also want to have a cost field in the rental line items table, as the prices could change over time and, for historical invoice purposes, you need to know the cost of each DVD at the time it was rented. -- _________ Sean Bailey "newbie2db66" wrote: Hi I'm very new to Access databases and I'm trying to set up a system for a DVD rental shop. At the moment I have my tables set up as below, but I've been told that this is not normalised and won't work properly. Apparently I need to add another table. I'm also not sure what type of relationships I should have. tblCustomer Customer ID PK Title Surname Forename Address Town Postcode Contact Number Film Genre Payment Method Join Date Paid Membership? tblRental Rental Number PK Customer ID FK DVD ID FK Nights Rented Date Rented Date of Return Cost Per Night Normalisation tblDVD DVD ID PK Film Genre DVD Title Main Actor Film Length Rating 5 Star Rating Any help would be much appreciated. Thanks |
#4
|
|||
|
|||
Normalisation and Relationships Problem
Thanks for your responses guys. Made me have a think about it some more and
come up with some ideas. Think I will create another table so that a customer can rent more than one DVD at once. |
Thread Tools | |
Display Modes | |
|
|