A complete library management database schema designed for public libraries, school libraries, or digital lending platforms. Includes book catalog with categories, member registration, loan tracking with due dates and late fees, and a reservation system for in-demand titles. Suitable for building library management software, digital lending platforms, or book inventory systems.
A book and a copy of a book are different things, and almost every decision here follows from that. books is the title with its ISBN and publisher; book_copies is the physical object on the shelf, with a barcode, a condition and a location. You cannot lend a title — loans keys on copy_id, because what leaves the building is one specific object.
Reservations key on the title instead. reservations points at book_id, because a member waiting for a popular novel does not care which copy they get — the queue is for the work, and the system assigns whichever copy comes back first.
Fines reference both the loan and the member. The loan explains what the fine is for and the member is who owes it, which matters because a fine outlives the loan it came from: the book goes back, the debt stays until is_paid says otherwise.
This asymmetry is the heart of the schema. Lending has to name the exact object so its condition, location and return are traceable. Reserving must not, because a hold on copy number three would fail when copies one and two come back first. Modelling both the same way breaks one of them — the split is what makes the queue work while keeping the circulation record precise.
A single amount_owed column would be smaller and would answer nothing: which book, how late, has it been appealed. Each fine keeps its reason, its amount and is_paid, so a member's balance is a sum over unpaid rows. The cost is that the balance is computed rather than read, which for a number this small is not a cost at all.
The copy's status tracks the object — available, on loan, lost, in repair. The loan's status tracks the transaction — active, returned, overdue. A copy can be lost while its loan is still open, and a loan can be closed while the copy goes straight into repair. One column could not say either.
An open loan is one with a null returned_at, which makes the overdue query a comparison against due_date with no join. Moving returns into their own table would be more normalised and would turn the most frequent question a library asks — what is out right now — into an anti-join. The null is doing real work here.