3

I need to make database model about libraries. In that model I'm tracking more libraries and every library has it's own moderators. Every moderator can make categories of the books and than add books in categories (note: another library doesnt need to have same as categories of books as library where that moderator is working, every other library can have different categories of books .. ).

enter image description here

This picture represents how did I make it. But something is missing, think this is not good solution. I have also another tought.. To link tables "book" and "category_book" in N:M and than link "book" and "libraries" which from I can get everything I need. But, think it's also not the best solution because every library will in that moment has same categories of books.

Am I missing something or? Haven't met with this kind of problem yet so if you could help me I will be grateful

Whole design ...Note: it's not over, I can't move further beacuse of this problementer image description here

|improve this question|||||
  • Am I right in thinking a Book can only be in one Category (per library)? If so, why not have a Library_Category table, and then the Category_Book table (which can then link directly to Book as you mention) – Tersosauros Mar 26 '16 at 16:52
  • I have Library_categ table.. But, you are trying to say that book in the first place need a category which I will make with Category_Book and than when moderator makes a category in Library_Category he will be able to update the same table with book which is in the same _category_(defined in table Category_Book) as he is trying to update? – Superbrain_bug Mar 26 '16 at 17:05
  • Search for "Libraries" on this page - they have a whole section on them. – Vérace Mar 26 '16 at 18:54
3

Description

I'm synthesising that these are your constraints:

  1. One Book can be in MANY Category's, but only in ONE Category per Library.
  2. Each Library can have ZERO or more Category's, which they do not have to "share" with other Library's.

Solution

This should all be achievable via the following structure (ignoring your wider solution space with users, borrowing, etc):

Categorised Library Books ER Diagram

Note:  The link between library_has_category and book_in_library_category is a composite key of the library AND category keys together.

|improve this answer|||||
  • Yes, I think this is a pretty much what i have been looking for. Thank you I will post my finally solution so you can correct me if I am missing something out :) – Superbrain_bug Mar 26 '16 at 17:25
  • No problem! If this fully answers your question, then don't forget to Accept the answer. Otherwise you can keep editing/clarifying until it is 100% solved. – Tersosauros Mar 26 '16 at 17:28
  • 1
    Nah, I think it's ok, you did it well. Really appriciate this, thank you mate :) – Superbrain_bug Mar 26 '16 at 17:41
1

From what you're saying it looks like your business rules are as follow:

  1. A library can have one or more categories
  2. A category can have 0 or many books
  3. A book must belong to 1 or more category

If that's what your business rules look like, then do the following:

Library (1:n) -------- (n:n)library_category (n:n) -------- (1:n)category

Then for books:

category (1:n) ------(n:n)category_book (n:n) --------- (1:n) book

That way libraries will have different amount of categories and a book will belong to different categories.

You don't need a link between book and library because you can get that through library_category/category_book.

|improve this answer|||||
  • Thank You, think I will able to make something from Your and @Tersosauros answer :) – Superbrain_bug Mar 26 '16 at 17:27

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.