Database schema for a library

Question

I'm designing a library management system for a department at my university and I wanted to enlist your eyes on my proposed schema. This post is primarily concerned with how we store multiple copies of each book; something about what I've designed rubs me the wrong way, and I'm hoping you all can point out better ways to tackle things.

For dealing with users checking out books, I've devised three tables: book, customer, and book_copy. The relationships between these tables are as follows:

• Every book has many book_copies (to avoid duplicating the book's information while storing the fact that we have multiple copies of that book).
• Every user has many book_copies (the other end of the relationship)

The tables themselves are designed like this:

------------------------------------------------
book
------------------------------------------------
+ id
+ title
+ author
+ isbn
+ etc.
------------------------------------------------

------------------------------------------------
customer
------------------------------------------------
+ id
+ first_name
+ first_name
+ email
+ address
+ city
+ state
+ zip
+ etc.
------------------------------------------------

------------------------------------------------
book_copy
------------------------------------------------
+ id
+ book_id (FK to book)
+ customer_id (FK to customer)
+ checked_out
+ due_date
+ etc.
------------------------------------------------

Something about this seems incorrect (or at least inefficient to me) - the perfectionist in me feels like I'm not normalizing this data correctly. What say ye? Is there a better, more effective way to design this schema?

Thanks!

Answer 1

It's an OK schema. However, it doesn't model the possibility that a work can have several different presentations -- that is, a book can have many editions (and translations, and formats).

How you slice this -- the granularity you use -- depends, as always in data modeling, on on your usage. In other words, is it "true", for you, for your usage, that a German translation of Alice in Wonderland is "different" from the English orioginal? (Yes, probably). Is a paperback version "different" from a hardcover?

The simple answer to this is to just use ISBN as the key -- letting the publishing industry makes these decisions for you. Then, anything with the same ISBN is equal and fungible.

You may also want to model something like "acceptable substitute", "this ISBN is an acceptable substitute for that one, because the only difference is binding" or "this ISBN (Darwin's Origins 6th edition) is the sixth edition of that one (darwin;s original The Origin of Species)", or "this ISBN is a translation of that one" or even "this ISBN (the KJV Bible) is similar to that one (the NIV Bible)." This gets into subtle gradations.

The other, more fundamental problem, is that copies of the same book are conflated with checkouts of those copies. If you unfortunately ordered have 10 copies of, say, Herb Schildt's The Annotated ANSI C Standard, but they are mercifully not checked out because students at your uni read Pete Seebach's excellent review of that terrible book, what is the customer_id for those copies in book_copy?

You want (at least) tables for book (work, isbn); copy; user; and the relation user-checksout-copy.