3

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.

  1. Borrower
  2. Loan
  3. 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.

E/R diagram

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.