Normalisation and Relationships Problem | Thread profile

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...

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...

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...