Designing a simple library database

I am currently developing a simple book library system in ms access 2007 and need some help with the database design. I want to register books, borrowers and loans, so I assume that my database could consist of these three entities.

• Borrower

• Loan

• Book

The relationships between the tables are as so:

Borrower has a 1 to many relationship with Loan.

Loan has a 1 to many relationship with Book.

The problem with this approach is that you can not create a row in the Book table without assigning a foreign key value; thus referencing the foreign key loanId of Book table with the related primary key in the Loan table. It breaks the referential integrity and it's not possible to create a book without relating it to a loaner.

So I think I need a join table between Book and Loan. This table should consist of the 2 primary keys from Book and Loan (a composite key). So there is a 1 to many relationship between Book and BookLoan and a 1 to many between Loan and BookLoan.

Is my way of thinking correct ? I can't figure out why I need the join/junction table between loan and book. Is there a more simple solution to my problem?

Thanks in advance :)

EDIT: I have added a E/R-diagram which has been corrected in respect to the answer, I have received.

I believe you are assigning the Foreign key on Book to Loan. It should be just the opposite and be on Loan to Book. The Book doesn't need a comparative loan but every loan needs a comparative book.

Both of your foreign keys should exist on Loan.

StackExchange Database Administrators Q#114619, answer score: 3