Database Design DVD Movie Collection Database

[chungiemo]

Hi Everybody,

Apologies if in wrong section

I am designing a DVD Movie Collection Database for my own personal use and requiring a little help.

Brief description: The key tables are tblMovies, tblLoans with the following key fields

MovieID (Autonumber)
MovieTitle
Status: Available or Not Available

LoanID
MovieID
MemberID
LoanStatus: Status Loaned or Returned

My query is there a way to store multiple Movies Title with the same Movie ID.

Scenario: DVD contains the movies Saving Private Ryan, Spiderman, Star Wars and a friend loans the DVD, I would like it to be in effect all three movies status “Not Available”.

I already know I could put all 3 movies in the one field MovieTitle, which will fix this, but this isn’t a good solution as this won't sort my movies into a true alphabetical order and movie count would not be accurate

If somebody has any tips or advice, or if my thinking is wrong please reply

[missinglinq]

You're correct; placing the three movie titles into one field would be a major mistake! I think what you need to do is to add one more field to the mix, say DVD_ID. That way, when the DVD is on loan, you can show that any movie associated with the particular DVD is unavailable. If you have multiple copies of a given DVD, each would have to have its own DVD_ID.

Welcome to TheScripts!

Linq ;0)>

[dafallus]

What I would suggest is making a third table, call it tblDisc or something. This table would have the following fields:

DiscID
MovieID
LoanStatus

This way you can keep track of all the movies on each disc and still have unique MovieIDs and titles. Depending on the database you're using, you can make MovieID a foriegn key. This way if LoanStatus of the disc changes you will have a way to update the LoanStatus of any movie on that disc.

You could also alter your tblMovies table and add a DiscID field.

The point is all you really need is a way to track a unique ID for the disc.