Normalistion

[jimeee]

HELP ME!!!!

Hi, I'm writing a database for a friend of mine. He has a collection of DVD's which he loans out.

I need a table which stores the DVD, the programs on it, the scenes on the program, the length of the scenes and a description of each scene.

Now here's the complicated bit

There may be 1 or more programs on each DVD
There may be 0,1 or more scenes in each program
AND there may be a 0, 1 or more descriptions for each scene

As you can appreciated there are many normalistion problems, especially as there are the users and the users loans of the DVD's to consider.

Any help is very, very VERY much appreciated and I thank in advance anyone who can help me. (Grovel)

Jimeee

 

[Graham T]

jimeee

To create this you will need to include a minimum of the following tables, which include the minimum amounts of fields. You should add the additional fields required:

tblUsers -
lngUserID (PK)
strFName
strLName

tblLoans -
lngDVDID (PK)
lngUserID (PK)
dteLoanDate (PK)

tblDVDDetails -
lngDVDID (PK)
strDVDTitle
intOverallDVDLength

tblProgramDetails -
lngProgramID (PK)
lngDVDID (FK)
intProgramLength

tblSceneDetails -
lngSceneID (PK)
lngProgramID (FK)
intLengthOfScene

tblSceneDescriptions -
lngSceneDescriptionID (PK)
lngSceneID (FK)
strSceneDescription

tblUsers >> tblDVDDetails (Many - To - Many) via tblLoans. One User can have Many Loans, Many Loans can have Many Users.

tblLoans >> Multi-field Primary Key. Included dteLoanDate as the same user could loan the same DVD on different occasions.

tblDVDDetails >> tblProgramDetails (One - To - Many). One DVD can include Many Programs.

tblProgramDetails >> tblSceneDetails (One - To - Many). One Program can include Many Scenes.

tblSceneDetails >> tblSceneDescriptions (One - To - Many). One Scene can have Many Scene Descriptions.

Please see the attached screen shot of the Relationships.

Can you also try to avoid posting the same question in two different forums . If you are unsure of which forum to use, the General one is your best bet.

Hope this points you in the right direction.

Graham

 

[jimeee]

Thanks a lot, I won't post the same message twice again.