Hi
I wonder if anyone can help me. I am trying to create a library database with 4 tables: books, loans, children and authors. I have created relationships between all these tables. However in my loans table 2 different children can borrow the same book on the same dates. Which clearly should not be possible. Could you please let me know how to stop this.
Thank you.
K

Not sure I get the problem. When the user tries to check out a book, you should run a search on the loans table detecting whether it is already on loan. If so, you should show him a msg stating, "Sorry, that book is already on loan." I assume you know this already, though, so I am not sure where you are running into a problem.

Is a loans table necessary, by the way? Instead, maybe consider having a few more columns in the Books table such as "OnLoan" (Yes/No column), "Date_Loaned", "Loaned_To", etc. Just my opinion, I'm no expert on database design.

i would go with jal's suggestion and put the extra columns in the books table. also include a DateReturned column. but do you have to track the history of all loans?

Good point about loan history. And I hope I didn't jump the gun there. I was just thinking that if you have a million books, and only a 100 out on loan, the loan table would be nice and small, perhaps saving space over my "extra columns" idea. Like I said, I'm no expert on database design.

Thank you for the suggestions but I have been told that I should have a relational database with a loans table and that I should not include the return_date, loan_date in the books table because this would mean I have not normalised my database enough. I have enforced the referential integrity but this does not stop me from loaning out the same book to 2 different persons on the same date. Any other suggestions would be greatly appreciated.

The solutions to your problem is to put an additional field in your books table called Available, make this a check box, with the default as checked, then when ever the book is loaned out or returned simply change the status of the check box as part of your loan/return procedure.

Use this check box to ensure that the same book is not loan out to a second user when it is still on loan.

If you need to track a books loan history you will need an additional table in which you will need to store the Book's ID and Loan and return dates, and who borrowed it.

Thank you for the suggestions but I have been told that I should have a relational database with a loans table and that I should not include the return_date, loan_date in the books table because this would mean I have not normalised my database enough. I have enforced the referential integrity but this does not stop me from loaning out the same book to 2 different persons on the same date. Any other suggestions would be greatly appreciated.
I think the issue is whether you need to track loan history. Let's suppose you don't. In that case, you can delete the book from the loans table when it is returned. If you take this approach, you can make the book-ID the primary key of the loans table and therefore it would seem impossible to loan out the same book to two different people.