Table Relationships

[babydave]

This is a simple video library database, 1 member can have many loans, 1 video can have many loans and 1 loan can have many videos, so with that in mind woud anyone be able to make my relationships right? because at the moment you cant take out more than one video on each loan?

thanks in advance

 

[KenHigg]

Maybe you should think more along the lines of a loan representing a transaction. Then a table with transactionDetails that points back to the transaction table and then also point to the video info table.

Say transaction has trans date, custID, etc. Then the transDetail table as a record for every video they borrow, a one to many. Then this transDetail table has a videoID fld that points to the video detail table, one to one relationship.

Think this will work?
Ken

 

[wazz]

I would firstly change the table name 'VIDEO TABLE' to 'VIDEO' and change the field name 'VIDEO TITLE NUMBER' to 'VideoID' (or VIDEO_ID). Also, remove all spaces from all of your names or add '_' between words.

OK, sry for that digression.

I would remove the realtionship VIDEO TABLE:LOAN TABLE and make instead a relationship between VIDEO TABLE:LOAN DETAILS (1:N) on VIDEO TITLE NUMBER.
I hope this helps.

 

[babydave]

yep ken i think that would work, as they can only borrow 3 videos at a time and i need the system to be able to say how many they currently have loaned out so i can use a query to restrict them from taking out more than 3.

 

[babydave]

this might work

 

[KenHigg]

So are you comfortable with how you have it now?

Ken

 

[babydave]

at the moment i have neither options setup if you'd be willing to setup the relationship how you suggested thatd be great

 

[KenHigg]

That looks close to me. You may want to remove Video Title Number from the Loan Table.

And if it were mine, I'd take the time to rename stuff. At a minimum, replace the upper case letters with lower case, replace spaces with an underscore and prefix the table names with 'tbl'. (Like tblLoans and tblLoanDetails)

Ken

 

[babydave]

if i remove video title number from the loan details table then where would the relationship from video table to loan details go? i have it setup now as you suggested and it makes sense, just confused about that one relationship now.

 

[KenHigg]

.
.
.
.

remove Video Title Number from the Loan Table

 

[babydave]

that was why i was confused

thanks a lot.

 

[babydave]

this is weird, 1 loan still cant have multiple videos. annoying

 

[wazz]

this is weird, 1 loan still cant have multiple videos. annoying

are the 'multiple videos' going into the LOAN DETAILS table with one video per row? (Each row will have the same LOAN ID but different LOAN DETAILS IDs).

 

[babydave]

i think that was my original plan but im open to any new ideas, basically i just want one loan ID to be able to hold information about multiple video ID's so someone could take out shrek, bruce almighty and zulu on one loan ID.

cheers for replying

 

[KenHigg]

What if someone wanted to check out Rocky, African Queen and say maybe, In the Heat of the Night?

(Sorry )
kh

 

[babydave]

if they did then theyd be equally good films

please help me

 

[KenHigg]

Sorry.

I think you have what would be the best set up. As far as limiting the number of videos, I think I would just have a message box pop up that advises the data entry person that the customer has exceeded three videos. That way they could override, in case there were extenuating circumstances...

kh

 

[wazz]

are the 'multiple videos' going into the LOAN DETAILS table with one video per row? (Each row will have the same LOAN ID but different LOAN DETAILS IDs).

just to clarify:

the 'multiple videos' (the movies being rented) should go into the LOAN DETAILS table with one video per row. (Each row will have the same LOAN ID but different LOAN DETAILS IDs).

so, first you enter a loan (LoanID is 1) then you go to LoanDetails and add details (note that LoanID in the tblLoanDetails is '1' for all three rows, refering back to LoanID 1 in tblLoan:

tblLoan:
LoanID/MemberID/Date/StaffID/etc

1/45/Date/Me/etc.

tblLoanDetails:
LoanDetailsID/LoanID/VideoTitleNumber/LoanQuantity

1/1/1(Shrek)/1
2/1/14(Bruce)/1
3/1/7(Zulu)/1

w
(i haven't refreshed this page for a while. i hope i'm not repeating anything)

 

[babydave]

aha this works

thanks very much for the clarification and for bearing with me hehe.