Skip to main content
Database Administrators

Return to Question

Tweeted twitter.com/#!/StackDBAs/status/641942851711074304
added e/r-diagram
Source Link
jimmythetulip
jimmythetulip
  • 33
  • 1
  • 4

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

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 :)

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

Source Link
jimmythetulip
jimmythetulip
  • 33
  • 1
  • 4

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.

  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 :)