A library management database schema has to keep two things apart that everyday language runs together: a book as a title, and a book as a physical object on a shelf. This one models a multi-branch lending library in 12 tables with 12 foreign key relationships, 2 junction tables and 3 context views, on PostgreSQL with snake_case naming. It covers the catalogue, the copies held at each branch, and the circulation of loans, reservations and fines. Every column, type, key and constraint below is read from the diagram, which you can open and edit at the end.

What tables does a library management database need?

publishers

The publishing houses that books are attributed to.

books

A title in the catalogue, identified by ISBN. Not a physical object - that is copies.

authors

A writer, with a free-text biography.

books_authors

Connects a book to each of its authors.

categories

A subject heading a book can be filed under.

books_categories

Connects a book to each category it belongs to.

branches

A physical library location that holds copies.

copies

One physical item of a book, held at one branch and identified by barcode.

patrons

A library member who can reserve and borrow.

reservations

A patron’s claim on a title, before any particular copy is chosen.

loans

One copy in the hands of one patron, with a due date.

fines

A charge assessed against one loan.

How the tables relate

Every relationship here is one-to-many with a cardinality of zero or many on the child side: a publisher may have no books in the catalogue yet, a branch may hold no copies, and a loan may never attract a fine. None of the 12 foreign key columns is nullable, so no child row can exist without its parent.

The two many-to-many pairs are books to authors and books to categories, and neither is a direct relationship: each is resolved through a junction table appearing above as two separate one-to-many rows. books is the most referenced table in the schema, with four children.

The three context views

A context view is a focused subset of the main diagram, showing one part of the model while the main view remains the single source of truth. This schema is divided into three of them, drawn as coloured boxes on the diagram itself. Nine of the 12 relationships stay inside a single view. The other three cross between views, and they form a triangle in which each pair of contexts touches exactly once.

catalog

Contains publishers, books, authors, books_authors, categories and books_categories - half the schema. Five of its relationships stay inside: a publisher’s books, and the two junction tables connecting books to authors and to categories. Two reach out of it, both from books, one into inventory and one into circulation. Nothing points into the catalogue from outside.

inventory

Contains branches and copies, joined by one relationship. It is the middle of the triangle: copies.book_id reaches into the catalogue, and loans.copy_id reaches in from circulation. Every physical item in the library is one row here.

circulation

Contains patrons, reservations, loans and fines. Four relationships stay inside: patrons to their reservations and loans, and loans to their fines. Two reach out, and they land in different places - reservations.book_id into the catalogue and loans.copy_id into inventory. That split is the schema’s central design decision, and it is the subject of the next section.

The three crossings are books to copies, books to reservations, and copies to loans. Counting foreign keys as they pass between groups this way is the same reading the Context Map applies to a whole schema, one level above individual tables.

Design decisions

Why does a loan point at a copy but a reservation point at a book?

This is the decision the rest of the schema hangs from. A patron reserving a title does not care which physical item satisfies it, so reservations.book_id names the work. A patron borrowing takes one specific object out of the building, so loans.copy_id names that object. Collapsing both onto books - the shortcut most tutorial schemas take - makes it impossible to answer where a particular item is, or which branch is short of stock, because the loan no longer identifies anything you can put your hands on.

The two foreign keys also leave circulation for different contexts, which is why the split shows up as structure rather than as a naming preference: one crossing lands in the catalogue and the other in inventory.

Why is there a copies table at all?

Because books and copies answer different questions and have different uniqueness. A book is unique by isbn; a copy is unique by barcode. A book has a publication year and a description; a copy has a condition, a status and a branch. Without the second table the library could hold six identical items and have no way to say which one is lost.

Why do fines hang off loans rather than patrons?

A fine exists because of a specific borrowing event, so fines.loan_id records what it was charged for. The patron is still one hop away through loans.patron_id. Attaching fines directly to a patron would store the amount owed while discarding the reason, and the reason is what a member disputes.

Why do the junction tables have no id column?

books_authors and books_categories each have exactly two columns and both are part of the primary key. A book is linked to an author once or not at all, and the composite key enforces that without a second constraint. Both names are plural on both halves, which is the signal for this shape: the pair is the key, and the pairing is not a row in its own right.

Why are return_date and paid_at nullable when the other timestamps are not?

Six of the schema’s columns permit NULL, and two of them carry meaning rather than absence. loans.return_date is NULL exactly while the copy is still out, and fines.paid_at is NULL exactly while the fine is still owed - in both cases issue_date and assessed_at are NOT NULL and default to NOW(), so the row always records when it started and leaves the end open. The other four nullable columns are contact and metadata fields: publishers.phone, branches.phone, patrons.phone and books.publication_year. Deciding which columns admit NULL, and what NULL means in each, is what stops a nullable column from quietly weakening the rules around it.

Why do the circulation tables not carry created_at and updated_at?

Every catalogue and inventory table has the created_at/updated_at pair. None of reservations, loans or fines does. Instead they carry timestamps named after the event: reserved_at, issue_date and assessed_at, each defaulting to NOW(). Catalogue rows are records that get edited, so when they last changed is worth storing. Circulation rows are events, and an event’s important time is when it happened.

Why are the status columns check constraints rather than enums or lookup tables?

Five tables carry a status: five copy statuses, three membership statuses, four reservation statuses, three loan statuses and three fine statuses, each with a default. Writing them as check constraints rather than a Postgres enum type or a lookup table keeps the permitted values in the table definition and on the entity, where Schemity renders them alongside the column. The cost is that adding a fourth loan status requires a migration.

What changes in production

Five things this schema does not yet carry that a deployed library system will need.

Nothing prevents two active loans on the same copy. loans.copy_id has no unique constraint, and it should not have a plain one - a copy is borrowed many times over its life. What is missing is a partial unique index on copy_id restricted to status = 'active', which is the constraint that actually expresses “one item, one borrower at a time”. Without it the rule lives in application code and a race can break it.

copies.status duplicates state that loans already determines. Whether an item is borrowed is derivable from an active loan against it, and storing it separately means two places can disagree. It is cheap to read and it will drift; either maintain it in a trigger or derive it.

No indexes are defined. PostgreSQL indexes primary keys and unique constraints, but not foreign key columns. All 12 foreign keys here - including loans.copy_id, loans.patron_id and fines.loan_id, the three most-queried joins in a circulation desk - will be scanned until you add one.

No deletion behaviour is recorded, and there is no soft delete. The diagram carries no ON DELETE rule, so every foreign key falls back to NO ACTION, and no table has a deleted_at. A patron with loan history cannot be deleted at all, which may well be right, but it should be a decision rather than a default.

fines.amount is unconstrained NUMERIC, and authors.name has no unique constraint where publishers.name and categories.name both do. Pin the money column to NUMERIC(10,2), and decide deliberately whether two authors may share a name - as written they may, and the authors_id_key unique constraint sitting on the primary key does nothing to help.

Open this schema in Schemity Lite

The diagram is public and openable: open the library management schema in Schemity Lite. Drag the entities into an arrangement that suits you, add the columns your version needs, and export the result as SQL, Mermaid, DBML or an image. To work against a real database instead of a browser copy, the desktop app reverse engineers a live PostgreSQL schema into the same kind of diagram.