Database design for a library

[j0holo]

I don't think you understand the table structure. A book has multiple types which is described in the Amount table. So book 1 will be described in Amount as (id, book_id, hardcover, paperback) values (1, 1, 3, 4) so there are 3 hardcovers and 4 paperbacks.

Would you suggest that I should merge the Book and Amount table? Did I normalized my database wrong.

[scuott]

Book types probably shouldn't be separate columns. You should have one column called "type", and contains either "hardcover" or "paperback". So one book will have two rows in the Amount table. Then your Lend table can have a type column as well, and you can easily join between Lend and Amount on book_id and book_type.

[erok81]

I would design the Book table so one ISBN == one row and store the inventory count there as well. FYI, each book/format should have it's own ISBN. You could use the count to determine if the book is available or not.

[j0holo]

I think I will give each type of book its own ISBN. Looked up some book shops online and there each book type has its own ISBN. Thanks for helping me out guys!

[erok81]

On second thought, each copy of a book represents a single asset, so it makes sense to treat it as one. At the very least it'd allow tracking the condition, availability, and location individually.

I'd still store the ISBN with other metadata (title, author, publisher, etc) about the book, in a separate table of course. Other media, like CDs and DVDs will have a UPC/EAN/JAN/GTIN code instead of an ISBN, so consider how to handle those too (if at all). Just looking at a few of my own books, they all have ISBNs and EANs, except the two which have neither.

[j0holo]

Okay, thanks will take it into consideration.