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.


Authors
• Write books
• Have publishers
Publishers
• Publish books
• Have addresses and phone numbers
Books
• 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
Cardholders
• 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.

Thanks

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!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.