Need help understanding the correct way of making relations between many to many tables

Question

I need to know if my relationships made between many to many tables are correct. Books_Authors = Books should be able to have multiple authors, and authors should be able to be assigned to multiple books, correct?

Loans table, my tought was that one User should be able to make multiple loans, but one copy of a book should only be able to be loaned once at a time. correct?

is my relations and keys set up correctly to uphold this logic?

Answer 1

First of all, the design is pretty good! You're on the right track that's for sure. A couple of (opinionated (which is the best you can do because there's no one perfect design)) comments:

1. Each table should have a Primary Key. The cross referencing tables Book_Authors, Book_Categories do not current have a PK. I would add an additional surrogate primary key to each table along with unique column constraints spanning the cross referencing columns.
2. Users are currently limited to 1 Role. This can lead to having to deal with hierarchies in procedural code. Would be better imo to create a User_Roles table.
3. Add additional CreatedDate columns where currently missing.
4. Add unique constraints to tables with PK's. For example, Books could have unique (BookName and Author).
5. Loans table could/should have the actual return date as well.

Answer 2

As a general approach, that looks like a fantastic start.

Very good pickup on the 'Copies' table - I think most people would miss that first try.

There are a few issues with the correct relationships (Foreign keys) in your many-to-many tables (Books_Authors, and Loans) - I suggest you have a look at them.

One of the questions I think you need to think about is "Do we want the data to represent the current status only, or do we also want to keep a history?"

Your answer to that will affect design - particularly around the Copies and Loans tables.

If you're only representing current status, then you probably don't need the Loans table. Instead, against each Copy, just save the User_ID and relevant dates. When it's loaned out, update the User ID and dates; when it's returned, NULL these dates.

However, if you want a history (which I'm guessing you do), then you probably need to start adding a few things

• Loans - status of loan. This could be inferred by dates (in/out) but it's often useful to have a status as well. Also, what happens when a book is lost and a person pays to have it replaced?
• Think about the PK of Loans - if someone borrows the same book multiple times (sorry - same copy of the book, multiple times over time), how will you store it?
• Copies - status. What happens when they get lost or they just get so tattered that they must be discarded? (Also other possible statuses e.g., what happens when you pre-order copies of books?)
• Copies - some way to link to the actual physical copy of the book (in libraries in the past, they were barcodes sticky-taped to the spine of the book)

A couple of other things to think about

• You book has an 'author' field and then many-to-many to authors. This is OK... but may not be what you want
• Libraries (at least in the past) used the Dewey decimal system for books? You may want to add this too.