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.
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.