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