Table Help

[brb8036135]

Hi im a newbie here and am looking for some help with my tables and relationships. Im trying to create a library loan system but none of my relationships seem to work.

Customer table
CustomerID Autonumber
FirstName Text 20
Surname Text 20
Address Text 100
Telephone Text 15
PostCode Text 8
DOB Number 8
Membership Type Text 8

Loan Table
CustomerID Autonumber
ProductID Autonumber
Loan Date Number 8
Return Date Number 8
Fine Currency
FinePaid Boolean

Product table
ProductId Autonumber
ProductTitleId Number
Medium Text 4
Condition Text 1
Replacement Value Currency

Product Title Table
ProductTitleId Autonumber
Title Text 50
Author/Director Text 20
Genre Currency

The underlined fields are the primary keys

The relationships between the tables are
one to many customerId(customer table) to customer id (loan table)
one to many productid(product table) to product id (loan table)
one to many producttitleid(producttitle table) to producttitle id (product table)

Anyone tell me anythings wrong? to change? Im extremely confused with it all?!

Thanks

 

[ColinEssex]

The CustomerID in the loan table is a foreign key that links with the Customer table therefore it shouldn't be an Autonumber, it should be the same as CustomerID in the customer table.

Likewise the productID.

Also, why have you got a Product Title Table? shouldn't that data be in the Product table as its the descriptor of the product (title etc)

Col

 

[Brian1960]

Foreign Keys

tblCustomer
CustomerId AutoNumber (this is correct)

The Loans table should be

tblLoans
LoanID AutoNumber
CustomerId Long (as a foreign Key. Indexed & allow duplicates)
ProductId Long (as a foreign Key. Indexed & allow duplicates)
etc

The rest looks okay but get rid of spaces in table names and field names. You'll regret this later if you don't.

 

[Brian1960]

Product Tabel

If you have Book and it is called
"Famous Five go Mad"

There may be 10 copies in a liabrary.
Hence

tblProductTitle
PTID autonumber
Title
ISDN
etc

and

tblProduct
PID auto
PTID number (FK from tblProductTilte)

 

[Mile-O]

Why are all your dates in as numbers and not dates?

 

[dopedealer]

Customer Table:
Address 100 text

What if the address is more than 100 characters? Use a Memo field for the Address.

 

[Mile-O]

Personally, I'd break the address down into smaller components. It's probably the one instance where I wouldn't care if I had blanks.

 

[dopedealer]

Personally, I'd break the address down into smaller components. It's probably the one instance where I wouldn't care if I had blanks.

Exactly, i would do the same, have address line 1, address line 2 and address line 3 with the First Line being mandatory, also a separate field for the City and Postcode. This helps when your doing Shell Letters or automated responses.
Chris

 

[brb8036135]

Thanks to everyone for such quick replies!! Ive made changes and managed to get it working now. One more question though guys, ne idea how i would do a query to get the most popular or few most popular books? I dunno which criteria to use.

 

[brb8036135]

I tried that query but it keeps coming up with "The Select statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect"

Do you know what i need to do to correct it?

 

[Mile-O]

Change descending to DESC

 

[brb8036135]

Tried that but it still doesnt work. Ne more ideas?

 

[ColinEssex]

shouldn't there be a From tblLoans in it? or whatever the table name is


Select TOP 5 ProductID, Count(*) As LoanCount
FROM tblLoans
Group By ProductID
Order by Count(*) desc;

Col

 

[Mile-O]

And I had other things on my mind.

 

[ColinEssex]

It was past my bedtime

And I had other things on my mind.

Gosh!!! I actually picked up a slight oversight in Pats code - and Mile-O missed it too!!! now I feel honoured to be involved in a very rare event.

Col