1

enter image description here

I'm going to implement a library system as a web application. And I decided to use this library schema. But there are some problems which I don't understand.

  1. The Books entity, is it possible to use isbn as a primary key?
  2. Is there a possible relationship between the Books entity and the Libraries entity? Is it a correct many-to-many relationship? I think that any book must be in only one library. So it is considered that one-to-many is more appropriate.

1 Answer 1

2

Your model completely ignores the fact that books come in multiple copies.

Each copy of a book can only be in one place at one time, but each place could have multiple copies of any given book.

Since each copy of a book will have exactly the same ISBN, the answer to your first question is: "No, ISBN is not a sufficient primary key for Books."

What you need to account for is a Title entity. This is going to be a combination of what you're thinking about for your Books entity. A Title is an abstract object. It will have a title, authors, publication date, and so forth. You might even want to consider making a distinction between Title and Edition, since a book can be republished many times with variations each time.

Whether or not you want to distinguish between editions of a title, you must at least distinguish between Title and Books. The answer to your second question is that each physical book will be in one place (either at a Library or on loan to a Member - or in transit between branches, or lost, etc. - depending on how sophisticated you want your model to be).

On the other hand, Titles, (or Editions) will be many-to-many with locations.

4
  • How would you label individual (electronic) copies of a book? Add that to your answer and I would probably upvote it.
    – Tom Au
    Apr 4, 2016 at 16:30
  • 1
    @TomAu Naming is one of the hardest parts of data modelling. I would be inclined to use the term Book for the physical volumes. As I noted in my answer I would call the abstract collection of all printed copies containing the same content Title. If someone thought Book was too ambiguous, I think libraries often use the term Copy.
    – Joel Brown
    Apr 4, 2016 at 16:41
  • I didn't make myself clear. Suppose there are 1000 copies of a book in (electronic) circulation. How would you number them 1 to 1000?
    – Tom Au
    Apr 4, 2016 at 16:52
  • @TomAu I think I would number them with a meaningless unique number encoded in a barcode sticker glued inside the cover of the book. I don't think each copy needs to have an "external" (i.e. human-friendly) identifier. This isn't necessary since people generally don't care about which copy of a book they have. The copy ID is relevant to the system so it should be assigned in a way that is convenient to manage. Pre-printed unique ID barcode stickers are convenient for the librarian who is cataloging the book. You can glue in the sticker, scan it, and then associate it with the title.
    – Joel Brown
    Apr 4, 2016 at 17:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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