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

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

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

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.