Book Loan database

[meekychunky2004]

Hi all

This is a database for my final year at Uni. Ive been trying to build this database and am not getting very far as I know very little about Access. I have put the tables together and created the relationships.

My thoughts are that before I go any further i ought to check with you guys that Ive got the basics right. Please can you look at the attached image and let me know your thoughts.

Thanks in advance

 

[Mile-O]

• I would split the author's name into Forename, Initial(s), and Surname.
• DateDueBack isn't needed as it should be calculated in a query, form, report. I'd expect a LoanDuration field - it's location would depend on what sort of books have different loan periods (i.e. reference 2 weeks, novels, etc. 4 weeks) Each book may have a variable LoanDuration so this field would go in the Book table as its dependant on the BookID; certain genre may control the duration so, if this is the case, then the LoanDuration should go here, etc.
• Is establishment dependant upon the borrower? If there are going to be more than one borrower from a certain establishment then that's deserving of a new table
• Other thought: with libraries I know that different restrictions may be in place on the number of books a person can borrow. This may not be necessary in light that it's just a uni project.

 

[meekychunky2004]

Thank you for your comments. Although this is my Uni projetc it is also for a real live company that loans foreign books to schools with a set rental period of 4 months, which can be extended for 4 mths at a time. The borrower is the establishment (should change this to contact name).

I have a field in the language table called BookCode generated from the first 3 digits of the language, should this be used as the Key field or is it ok like this.

The book ID should be made up from the BookCode with 5 incremental digits ie: FRE00001 FRE00002 GER00001 GER00002 - i have read around and still cant get my head around it! Any thoughts

• I would split the author's name into Forename, Initial(s), and Surname.
• DateDueBack isn't needed as it should be calculated in a query, form, report. I'd expect a LoanDuration field - it's location would depend on what sort of books have different loan periods (i.e. reference 2 weeks, novels, etc. 4 weeks) Each book may have a variable LoanDuration so this field would go in the Book table as its dependant on the BookID; certain genre may control the duration so, if this is the case, then the LoanDuration should go here, etc.
• Is establishment dependant upon the borrower? If there are going to be more than one borrower from a certain establishment then that's deserving of a new table
• Other thought: with libraries I know that different restrictions may be in place on the number of books a person can borrow. This may not be necessary in light that it's just a uni project.

 

[Mile-O]

My personal preference is to always use an Autonumber as Primary Key. If I have instances where a unique identified is a text field (i.e. FRE00001) then I wouldn't have it as a primary key as, when the database grows, querying takes longer due to the slower indexing (amount of bytes used to store a string compared to those used to store a Long Integer being used as comparison on the relationship).

If I have a text field where each field will have a unique value then, as said, I wouldn't make it the Primary Key; I'd Index the field like so: Yes (No Duplicates).

I don't know what others would do but I'd still ditch the DateDueBack field and have a LoanDuration field as these things can change and it's easier to update one field with a duration rather than have to go in to a hardcoded form and change the code that calculates and writes the DateDueBack to the table.