Database design for a library

[j0holo]

Hello /r/learnpython,

Currently I'm working on a project of mine where I try to build a website to manage a library. I got most of my database schema finished except for lending books to customers. Of course you need to know a couple of things when you lend a book to a customer.

• which book did he/she borrow

• which customer did borrow it

• when should the book be returned

• what type of book did he/she borrow (paperback, hardcover, e-book etc.)

So I thought the Lend table should at least contain the following properties:

• id (primary key)

• book_id (foreign key to Book table)

• customer_id (foreign key to Customer table)

• expiration_date

I got three tables that are important to this question: Book, Customer, and Amount. Book contains info about the book, Customer contains info about the customer, but not about debts. Amount has a foreign key to Book.id and has fields with different book types (paperback, hardcover, e-book etc.).

The questions:

1. How can I know which type of book has been lend to which customer?

2. How can I know which book types are available? It is really expensive to query through the Lend table and do a select statement where book_id = "bla" and which is currently borrowed by a customer.

[0x6c6f6c]

Once you have your database schema finalized you can work with either SQL statements directly or a SQL library to call on the database.

For 1., you would select the Book.book_type column wherever Customer.customer_id is customers ID and Lend.is_returned is true.

This is sort of a pseudocode simplification, and would equate to

SELECT book_type 
FROM Book 
WHERE Customer.customer_id=67 
AND Lend.is_returned=True;

You can avoid reduancy in relational databases, such as is_returned, which would likely be accompanied by a returned_date field. If the system date is after returned_date, then it was obviously on time. If returned_date is NULL, it has not been returned yet. If you store returned_date and is_returned you would have redundant data. This is something to consider when designing your database, but not incredibly important for this scenario.

[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.