ErdDocs

Example schemas / Library Management System

Library Management System ER diagram

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

Below, ErdDocs draws this schema from its SQL, every table and every relation. The Data Dictionary tab has the printable reference, complete and free on this page. The SQL is further down.

Your schema

Hover a table to light up its relations, and click to pin them.

What each table does

  • books / copiesThe catalog entry versus the barcoded physical item. Most library schema questions come down to this split.
  • authors / book_authorsMany-to-many, because co-authored books break a direct author_id immediately.
  • memberscard_no is the human-facing identifier, the same pattern as a patient MRN.
  • loansAttach to the copy. You must know which physical item is out and overdue.
  • reservationsAttach 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.
  • A nullable returned_on does the work of an is_returned flag and records the date in the same column.
  • ISBN is unique but nullable, because 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. You are free to copy it.

Library Management System ER diagram: 7 tables and 7 relations, drawn by ErdDocs from the SQL below.Library Management System ER diagram: 7 tables and 7 relations, drawn by ErdDocs from the SQL below.
The diagram ErdDocs generates from this SQL.
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, so lending becomes a license question. Either add a licenses table parallel to copies (N simultaneous loans allowed), or put loans directly on books with a concurrency check in the application.

More example schemas