Video Shop Database

[zed1]

video shop database

Making a back-end database for video Rental shop web-site where user can search Dvd by Catergory and ask to borrow it. All info must be stored in relevant database.

How many tables do i need
1 Customers (should this include Username and password)
2. Dvd's Title,artist, rating
3. Catergory Horror,comedy,action etc
4. Loan ( not sure what attributes to have)

 

[jfgambit]

This should get you going in the right direction...

tblCustomers
CustID (Primary Key)
CustFName
CustLName
CustAddress
CustPhone
CustActiveDate
CustCancelDate

tblDVD
DVDID (Primary Key)
DVDName
DVDCopyDate
DVDProdCompany
DVDMinutes
RatingID (Linked One-to-Many to tblRating)
CatID (Linked One-to-Many to tblCatagory)

tblCatagory
CatID (Primary Key)
CatagoryDesc (i.e. Horror, Comedy, Drama)

tblRating
RatingID (Primary Key)
RatingDesc

tblRent
CustID (Linked One-to-Many to tblCustomers)
DVDID (Linked One-to-Many to tblDVD)
CheckOutDate
ReturnDate
ReturnNotes

 

[zed1]

what about user names and password where would i store them

 

[Kraj]

what about user names and password where would i store them

In the customers table, like jfgambit showed you.

However, the categories table is incorrect. The DVD table should NOT have categoryID in it. Instead, the category table should include DVDID. This will allow one DVD to have multiple category designations. The category itself does not need an ID, unless you choose to store more information about a category than the name.

 

[Mile-O]

The DVD table should NOT have categoryID in it. Instead, the category table should include DVDID. This will allow one DVD to have multiple category designations. The category itself does not need an ID, unless you choose to store more information about a category than the name.

I would argue against that, Kraj. The category should not have the DVDID. If one category is required per DVD then jfgambit has it spot on. If there are more categories required then a many-to-many relationship needs built in.

 

[jfgambit]

Attached is a sample Db that should help you get started...

(Mile-O changed it to many)



There need to be a number of additions to this, but it should point you in the right direction...

HTH

 

[Kraj]

I would argue against that, Kraj. The category should not have the DVDID. If one category is required per DVD then jfgambit has it spot on. If there are more categories required then a many-to-many relationship needs built in.

Then I think you misunderstood me. The DVDID should be in the Category table because that table is lisiting the categories asociated with each DVD. There is no reason to have a table with only CategoryID and Category description.

If each DVD has one and only one category associated with it, then jfgambit is right, just pop the category in the DVD table, but there is still no need for a category ID and seperate table.

In addition, jfgambit's sample database has several tables that consist of nothing but an ID and a description, or even just an ID. These kinds of tables are almost always unnecessary.

 

[jfgambit]

In addition, jfgambit's sample database has several tables that consist of nothing but an ID and a description, or even just an ID. These kinds of tables are almost always unnecessary.

You are correct. You could add those directly to the tables as list values...which is why I added:

There need to be a number of additions to this, but it should point you in the right direction...

It was just meant to get zed1 moving in the right direction...

That and I wanted to see if anyone else likes "Die Hard"...

 

[Kraj]

It was just meant to get zed1 moving in the right direction...

Ah, I forgot about that disclaimer.

And yes, Die Hard is one of those movies I never get sick of.

 

[Mile-O]

I actually prefer to have the lookup tables separate for the reason that I don't want to have to come back and edit Value Lists to add new details in later - I prefer my users to assume an administrative control over the frontend and the data in the backend so that, should a new genre be invented then the users can add them themselves.

That sort of flexibilty is better, in my opinion.

 

[Kraj]

That is a fair point, but couldn't the same thing be accomplished with a simple code routine?

If your value lists pull their choices from the fields they populate, then all you would have to do is have a verify new list entry prompt OnNotInList. User says 'yes' and boom, you've got a new list entry available.

Now that I think about it, though, the flaw there is that a blank database would have to be prepopulated with some dummy records for the list boxes to initially use. And anyone could change the list; you wouldn't be able to set permissions for something like that.

There are certainly pluses and minuses, then. Hmmm...something to think about.

 

[Mile-O]

That is a fair point, but couldn't the same thing be accomplished with a simple code routine?

If you are implying changing the Value List of the table via code then that will cause problems when there is more than one user using the database and goes against the reason for a frontend and backend.