A library management database schema has to keep apart two things 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 14 foreign keys and 2 junction tables, grouped into a catalogue, an inventory and a circulation desk. It was designed in PostgreSQL and converted to MySQL, and both SQL files are linked beside the article, ready to import. Below: the decision the design turns on, what each table is for, one book followed from hold to fine in four queries, what the conversion to MySQL changed, and four rules to add before you use it.

A book is not a copy

Everything else in this schema follows from one split. books is the title, unique by ISBN. copies is a physical item, unique by barcode, held at one branch. A patron reserves a title and does not care which copy satisfies it, so reservations.book_id points at books. A patron borrows an object, so loans.copy_id points at copies.

Most tutorial schemas collapse both onto books. That works until someone asks where a particular item is, which branch is short of stock, or which of six identical copies went missing: a loan that names a title names nothing you can put your hands on.

A hold sits between the two. It starts against the title, and when a copy is set aside for it, reservations.copy_id is filled in and the hold moves to ready at its pickup_branch_id until expires_at.

What tables does a library management database need?

Twelve, in three groups. The columns worth knowing are listed here; the full reference is at the end.

All 14 relationships are one-to-many. Every foreign key column is NOT NULL except reservations.copy_id, and every one has an index declared in the schema: PostgreSQL never indexes a foreign key column by itself, and MySQL builds one silently only when none is declared, so declaring them keeps the two files alike. The two many-to-many pairs, books to authors and books to categories, are resolved through junction tables.

The groups are drawn as coloured boxes on the diagram, and each is also a context view: a sub-diagram that shows only its own tables, so the circulation desk can be read without the catalogue. Catalog holds the six title tables, inventory holds branches and copies, and circulation holds patrons, reservations, loans and fines. Five foreign keys cross between the groups: copies.book_id, reservations.book_id, reservations.copy_id, reservations.pickup_branch_id and loans.copy_id. Every other key stays inside its group. Counting crossings like this is the reading the Context Map applies to a whole schema.

Following one book through the library

Four questions a circulation desk asks every day, answered by the schema as it is. Each query ran unchanged on PostgreSQL 18.3 and MySQL 8.4.11 against the two download files, with one title, two copies at two branches and two patrons.

Ben places a hold on Pride and Prejudice: one row in reservations, against the title, for pickup at Riverside. When a copy comes free, the desk asks who is next:

SELECT p.first_name, p.last_name, r.reserved_at
FROM reservations r
JOIN patrons p ON p.id = r.patron_id
WHERE r.book_id = 1 AND r.status = 'pending'
ORDER BY r.reserved_at
LIMIT 1;

Ana borrows copy C-0001 from the Central branch, and her due date passes. Which loans are overdue, and where is the item?

SELECT b.title, c.barcode, br.name AS branch, p.email, l.due_at
FROM loans l
JOIN copies c ON c.id = l.copy_id
JOIN books b ON b.id = c.book_id
JOIN branches br ON br.id = c.branch_id
JOIN patrons p ON p.id = l.patron_id
WHERE l.returned_at IS NULL AND l.due_at < CURRENT_TIMESTAMP;

The answer names the barcode and the branch, which is only possible because the loan points at a copy. Overdue is computed, not stored: there is no loan status to forget to update. Which copies of the title can go on the hold shelf? One that is in service and has no open loan:

SELECT c.barcode, br.name
FROM copies c
JOIN branches br ON br.id = c.branch_id
WHERE c.book_id = 1
  AND c.status = 'in_service'
  AND NOT EXISTS (
    SELECT 1 FROM loans l
    WHERE l.copy_id = c.id AND l.returned_at IS NULL
  );

It returns R-0001 at Riverside, which is set aside for Ben. Ana returns her copy late: returned_at is set and a row goes into fines against her loan. What does she owe, and for what?

SELECT b.title, f.reason, f.amount, f.assessed_at
FROM fines f
JOIN loans l ON l.id = f.loan_id
JOIN copies c ON c.id = l.copy_id
JOIN books b ON b.id = c.book_id
WHERE l.patron_id = 1 AND f.status = 'unpaid';

Because the fine hangs off the loan rather than the patron, the answer carries the title and the reason, and those are what a member disputes.

Why is it built this way?

State lives in timestamps. A loan is open while returned_at is NULL, a fine is owed while paid_at is NULL, a hold waits until expires_at. An earlier version of this schema kept loans.status and an available/borrowed value on copies.status beside those columns, and in testing the copy still read available while it was out on loan. Now copies.status holds only what a timestamp cannot: whether the item is in service, in repair, lost or withdrawn.

The database checks what it can. Statuses are check constraints rather than an enum type or a lookup table, so the value lists sit in the table definition, where Schemity shows them on the entity. Three more checks guard the numbers: due_at > issued_at, renewal_count >= 0 and amount > 0. Money is NUMERIC(10,2), so a computed fine of 2.805 is stored as 2.81 on both databases.

Deletion is decided. Deleting a book or an author removes its books_authors and books_categories rows (ON DELETE CASCADE), because a pairing means nothing without both halves. Every other foreign key is NO ACTION, so a patron, copy or loan with history cannot be deleted while that history exists. For a library, lending history is the record.

Junction tables have no id. books_authors and books_categories are two columns each, both in the primary key, so a book is linked to an author once or not at all with no second constraint. The primary key’s leading column serves lookups by author; the extra index on book_id serves lookups by book.

What changed when the schema was converted to MySQL?

The tables, keys, checks and indexes carried over one to one; what changed is how MySQL spells them. Schemity wrote the MySQL file from the PostgreSQL design and listed each change as a comment at the top of the file:

The one that changes behaviour is the timestamp. TIMESTAMPTZ stores an instant; DATETIME stores the clock reading it was handed. A row written at 07:33 UTC from a session set to +07:00 stored created_at as 14:33 on MySQL and as 07:33+00 on PostgreSQL. Set every MySQL connection to UTC, and due_at < CURRENT_TIMESTAMP compares like with like.

The MySQL file also carries each table’s description as a table COMMENT; the PostgreSQL file does not. Both files were imported into empty databases, PostgreSQL 18.3 and MySQL 8.4.11, and both produced 12 tables and 14 foreign keys.

Four rules to add before you use it

The tables are complete; these four rules cross rows or columns, so they need more than a column definition. Each was tested both ways on both databases: the bad row fails, and the legitimate row after it still goes in.

1. One open loan per copy. Without it, two INSERTs lent copy R-0001 to Ana and Ben at the same time. A unique constraint on copy_id is wrong, since a copy is borrowed many times in its life; the rule is about open loans only. PostgreSQL says that with a partial index. MySQL has no partial indexes, so the index is built on an expression that is NULL for returned loans, and a unique index allows any number of NULLs:

-- PostgreSQL
CREATE UNIQUE INDEX loans_one_open_per_copy
  ON loans (copy_id) WHERE returned_at IS NULL;

-- MySQL
CREATE UNIQUE INDEX loans_one_open_per_copy
  ON loans ((IF(returned_at IS NULL, copy_id, NULL)));

2. One active hold per patron per title. Ben could place a second pending hold on a book he was already waiting for. Holds that are fulfilled, cancelled or expired must not count:

-- PostgreSQL
CREATE UNIQUE INDEX reservations_one_active_hold
  ON reservations (patron_id, book_id) WHERE status IN ('pending', 'ready');

-- MySQL
CREATE UNIQUE INDEX reservations_one_active_hold
  ON reservations (patron_id, book_id, (IF(status IN ('pending', 'ready'), 1, NULL)));

3. The copy set aside must be a copy of the reserved title. A hold on Emma accepted copy C-0001 of Pride and Prejudice, because copy_id and book_id are checked separately. A composite foreign key checks them together, and the same SQL runs on both databases. While copy_id is NULL the key is not checked, so a pending hold still goes in:

ALTER TABLE copies
  ADD CONSTRAINT copies_id_book_id_key UNIQUE (id, book_id);
ALTER TABLE reservations
  ADD CONSTRAINT reservations_copy_book_fkey
  FOREIGN KEY (copy_id, book_id) REFERENCES copies (id, book_id);

4. A paid fine has a payment time. A fine went in as paid with paid_at NULL. The status and the timestamp say the same thing twice, so tie them together; this also runs unchanged on both:

ALTER TABLE fines ADD CONSTRAINT fines_paid_check
  CHECK ((status = 'paid') = (paid_at IS NOT NULL));

With all four in place the failures read duplicate key value violates unique constraint "loans_one_open_per_copy" on PostgreSQL and Duplicate entry '1' for key 'loans.loans_one_open_per_copy' on MySQL.

Open this schema in Schemity Lite

The diagram above the article is the same schema, live: open it to drag tables, read every column and export SQL, Mermaid or an image. To edit your own copy, open the library management schema in Schemity Lite. To start from a real database instead, the desktop app reverse engineers a live PostgreSQL or MySQL schema into the same kind of diagram.

Full column reference