ErdDocs

Example schemas / Library Management System

Library Management System ER diagram

The library management system schema teaches one crucial split: the book (a title in the catalog) versus the copy (a physical item on a shelf). Members borrow copies, not books; reservations queue on the book. Authors attach through a junction, since books have co-authors.

Your schema

Hover a table to light up its relations, click to pin. Switch to Data Dictionary for the printable reference version of this schema.

What each table does

  • books / copies — The catalog entry versus the barcoded physical item — the split every library schema question is secretly testing.
  • authors / book_authors — Many-to-many: co-authored books break a direct author_id immediately.
  • members — card_no is the human-facing identifier, same pattern as a patient MRN.
  • loans — Attach to the copy — you must know which physical item is out and overdue.
  • reservations — Attach to the book — the member wants the title, whichever copy returns first.

Design decisions worth copying

  • Loans point at copies, reservations point at books. Mixing the two levels is THE classic library-schema mistake, and it makes “who has this exact item” or “notify next in queue” unanswerable.
  • returned_on nullable does the work of an is_returned flag and records the date in the same column.
  • ISBN is unique but nullable — old and internal items exist; a NOT NULL isbn turns real data into constraint violations.

The SQL

PostgreSQL dialect; paste it into the tool above (it is already there), into your own database, or into a migration. Copy freely — example schemas are meant to be taken.

CREATE TABLE authors (
    id         bigserial    PRIMARY KEY,
    full_name  varchar(120) NOT NULL
);

CREATE TABLE books (
    id        bigserial    PRIMARY KEY,
    isbn      varchar(17)  UNIQUE,
    title     varchar(240) NOT NULL,
    published smallint
);
COMMENT ON COLUMN books.isbn IS 'NULL for pre-ISBN or internal items';

CREATE TABLE book_authors (
    book_id    bigint NOT NULL REFERENCES books(id),
    author_id  bigint NOT NULL REFERENCES authors(id),
    PRIMARY KEY (book_id, author_id)
);

CREATE TABLE copies (
    id        bigserial   PRIMARY KEY,
    book_id   bigint      NOT NULL REFERENCES books(id),
    barcode   varchar(20) NOT NULL UNIQUE,
    shelf     varchar(20),
    condition varchar(20) NOT NULL DEFAULT 'good'
);
COMMENT ON TABLE copies IS 'The physical item; a popular book has many copies';

CREATE TABLE members (
    id         bigserial    PRIMARY KEY,
    card_no    varchar(20)  NOT NULL UNIQUE,
    full_name  varchar(120) NOT NULL,
    joined_on  date         NOT NULL
);

CREATE TABLE loans (
    id          bigserial PRIMARY KEY,
    copy_id     bigint    NOT NULL REFERENCES copies(id),
    member_id   bigint    NOT NULL REFERENCES members(id),
    loaned_on   date      NOT NULL,
    due_on      date      NOT NULL,
    returned_on date
);
COMMENT ON COLUMN loans.returned_on IS 'NULL while the copy is out';

CREATE TABLE reservations (
    id          bigserial   PRIMARY KEY,
    book_id     bigint      NOT NULL REFERENCES books(id),
    member_id   bigint      NOT NULL REFERENCES members(id),
    placed_at   timestamptz NOT NULL DEFAULT now(),
    fulfilled   boolean     NOT NULL DEFAULT false
);
COMMENT ON TABLE reservations IS 'Queues on the BOOK — any returned copy can satisfy it';

FAQ

Where do fines fit?

A fines table referencing loans (the overdue loan is the cause) with amount, assessed_on and paid_on. Deriving fines on the fly from due dates also works for a course project — say which you chose and why.

How would e-books change this schema?

E-books have no copies — lending becomes a license question. Either a licenses table parallel to copies (N simultaneous loans allowed) or loans directly on books with a concurrency check in the application.

More example schemas