I'm trying to create a database for a public library and I'm running into a snag when trying to formulate my tables based on the requirements I've come up with.

• Write books
• Have publishers
• Publish books
• Have addresses and phone numbers
• Have titles and summaries
• Can be in series
• Have formats
• Are stored in sections by genre
• Have due dates(dates checked out and dates returned must be recorded)
• Can be checked out, placed on hold, requested
• Have unique library reference numbers
* Have ISBN's - each publication of a book as an ISBN ie: if a book is published in a different format, paperback, hardcover it gets a different ISBN
• Can request or place books on hold
• Can checkout ‘x’ amount of books
• Have addresses and phone numbers
• Can renew books

The snag I'm running into is how to arrange the books table. I technically have three different keys... I want to have a list of all books with their title and summary, regardless of format or ISBN or reference number. Each book in the library needs a reference number within the library system, we can have multiple copies of the same book (same ISBN) but each would get a unique reference number.

I'm just not sure how best to go about linking ISBN, format, and library reference number to books. I keep running into walls where I have multiples of the same data in a table. I've attached my rough ER diagram based on my requirements - its missing a few things, like a date attribute for the checkout field. And the phone numbers and addresses aren't on this version. But my main concern is getting the books settled into tables that work.


Attachments ERDiagram.png 46.6 KB
6 Years
Discussion Span
Last Post by BitBlt

Attached is what was my current design, everything seemed fine but having multiple tables using bid made insertion and queries kind of a bitch and I'm stuck as to an alternative method. Thus the above post.


You might consider separating the concept of "Book" from the concept of "Copy". Then you can assign a non-significant identifier "CopyId" as the primary key, then have non-identifying relationships to "Book" so that you get clean relations from "Copy" to "Library", "CardHolder" etc. while "Book" retains relationships to "Author", "Publisher" and so on.

Hope this helps!

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.