Normalisation and Relationships problem

[newbie2db66]

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 in the attached screenshot, 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.

Any help would be much appreciated.

Thanks

 

[KenHigg]

The only thing I can see is that you may need to account for multiple dvds in one rental transaction - ?

In which case you add a rental_dvds table.

 

[newbie2db66]

yes thats what I was told come to think of it! So how would the new table link in with the others? and would it be a many to many relationship?
Thanks for your help

 

[KenHigg]

Here's one way to think of it to figure out the relationships:

Each customer can have multiple rental transactions. Each rental transacation can have multiple dvds. Each dvd can have it's own return date. etc When you get all of it fleshed out in this manner then you can start creating tables and putting them together.

 

[MSAccessRookie]

Here's one way to think of it to figure out the relationships:

Each customer can have multiple rental transactions. Each rental transacation can have multiple dvds. Each dvd can have it's own return date. etc When you get all of it fleshed out in this manner then you can start creating tables and putting them together.

Creating a details table containing the DVD Specific information from the Rental table, and having a Foreign Key that points back to the Rental table should be what you are looking for. Then you just query all details that match a given invoice. On the form that would be represented as a sub-form with a parent-child relationship based on the Rental ID

KenHigg also points out that design in advance is better than on the fly. Like he says, take time to think about the basics.

PS. Does Each DVD have its own return date? I would think that each Rental agreement would have a return date, and that all DVDs rented under that agreement would need to be returned on the same date. Either way, you need to imagine all of the details before striking out and building tables.

-- Good Luck

 

[MarkK]

Consider something like this...

 

[KenHigg]

Looks about right to me Lag. Would you think a returned date would be needed in the tDVDRental table?

Edit: I would also store the current rental duration in that table - ?

 

[MarkK]

Ken: Ya, if you want to track stats on returns you'd definitely want the return date in tDVDRental. For rental duration there is a 'Due' field, which might be better named DueDate. Duration could be calculated, or the other way around.
Newb: It might also be worth noting that this is a textbook Many-To-Many relationship where many DVDs might be rented many times. This always requires three tables since there are three real-world datapoints involved. In this case...
1) DVD Object
2) Rental Event
3) The relationship between the two - slightly counter intuitive
A more intuitive example...
1) Student
2) Teacher
3) Class

 

[KenHigg]

I was thinking rental duration was the standard number of days the rental was for. After that late charges apply. These are different for some dvds. In which case you could make the argument that there needs to be a dvd type table where you store that info. So a dvd would be a certain type, like 'standard 3 day rental', etc. And all dvds of that type are $5.

It all depends on the original requirements. This stuff may be over kill or it may not

 

[newbie2db66]

Thanks so much guys. You've solved my problem and given me extra ideas on top. Fab!