Problem with table design

[dyingatmidnight]

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

[dyingatmidnight]

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.

[BitBlt]

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!