0

enter image description here

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?

2 Answers2

0

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.
SteveC
  • 5,955
  • 2
  • 11
  • 24
  • 1. Thank you for the great tip, i have added column Books_AuthorsID with PK to Books_Authors and Books_CategoriesID with PK to Books_Categories table. 2. My tought was, that in roles, i would have Administrator, Employee and Customers. and that administratos has all rights, employees almost all right, and customers only some rights. In that case, 1 user should only need 1 role, and 1 role should be able to contain many users, correct? 3. Great tip, i assume its for additional details / information about each subject. its added! 4,5 i will consider these. thank you! –  Oct 09 '20 at 17:55
0

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.
seanb
  • 6,272
  • 2
  • 4
  • 22
  • Status added to loans for sure! nice tip. I think, PK of loans is not wrong?, if they loan the same copy of a book, my tought was to extend the ReturnDate "DueDate" by an additional loan period. as far as when a book get lost or they get tattered, this is not something i have considered, this is way above my programming level i assume. also the whole idea of being able to pre order a book, means i have to be able to actually pre order books to the library, as far as my skills goes, this is more or less a storage app with some added features of customers being able to rent a book. –  Oct 09 '20 at 18:01
  • I am not sure what you mean about my relationships regarding the many to many tables being wrong? could you explain a little bit, as far as my toughts goes, i was thinking Loans has a one-to-one connection with copies because a copy of a book can only be loaned out once at a time, and loans can only include that copy of a book once at a time. ? <- help me out here if its wrong. regarding books_authors my tought was that one author can be added to many books, but books can also have multiple authors ? –  Oct 09 '20 at 18:06
  • Regarding your question about linking a copy to the actual book, isnt this done through the acutal bookID? or do you mean of adding a kind of barcode to the physical book? i love how you think, and appericiate your toughts. but my program will not nearly be as advanced as you think it to be, i do not have physical books, so dewey decimal system might not be what i am looking for here, but its a nice detail!. also why wouldnt my book need an author field when having a many to many relation with authors? a book should be able to have more authors, and authors should be able to have more books? –  Oct 09 '20 at 18:09
  • Re the keys in many-to-many tables: Look at Books_Authors (same applies to Books_Categories). There is currently no PK enforcing uniqueness. For the same book, you could have the same author multiple times. Instead, many-to-many tables frequently have their primary key as the combination of two fields e.g., BookID and AuthorID - with each one a foreign key to the relevant table. For the Loans table, your PK CopyID means each copy can only appear once in the table. It's fine if you don't want a history (e.g., delete when book is returned) but doesn't work if you want to keep a history. – seanb Oct 09 '20 at 20:50
  • Re authors field and many-to-many. For a given book, you may have the many-to-many relationship have 2 authors - 1 row/record for 'Jamie Smith' and 1 row/record for 'Bailey Lee'. As such, you don't need the field in the Books table, as the data is stored in Books_Authors. If you did also keep it in Books, and it only showed 'Bailey Smith', then at least one of your data points would therefore be incorrect. – seanb Oct 09 '20 at 20:57
  • Re barcodes and copies. I was assuming physical books. Imagine you had 2 copies of the same book: one 10 years old and tattered, and the other new. You lend the book to someone then they call up saying that half the pages were torn out. You go to look to the history - wouldn't you want to know *which* copy was taken out by each user? Note that if there's nothing distinguishing each copy from another, then instead of having a 'copies' table, you could just have a 'total number' (and maybe 'total_currently_available') field in Books as counters - no need to distinguish each copy. – seanb Oct 09 '20 at 21:04