Library Management ER Diagram

A library schema separates the catalog from circulation: books and authors describe what the collection contains, while members and loans describe who has what and when it is due. Because a book can have several authors, book_authors is a join table, and fines hang off individual loans rather than off members.

The catalog separates titles from physical copies even though this SQL keeps a single books table with a total_copies counter. A title owns an isbn, a category_id and bibliographic data; a copy has a barcode, a shelf and a condition, none of which belong to the title. If the library lends individual objects, the next step is a book_copies table where each row is one physical item, and loans.book_id becomes loans.copy_id. book_authors already captures the relationship the catalog cannot: an anthology has many authors, an author writes many books, and the composite primary key (book_id, author_id) prevents duplicate attribution.

Reading the diagram, categories to books and authors to book_authors are ordinary one-to-many edges; book_authors is the only many-to-many and appears as a small table between the two catalogs. Circulation is a short chain: members borrow through loans, and each loan points at exactly one book with borrowed_at, due_date and a nullable returned_at. fines.loan_id is UNIQUE, so a loan carries at most one fine row, while the fine itself can be settled in place through paid_at. That uniqueness is what makes the loan the unit of accountability rather than the member.

Real circulation breaks naive schemas in two places. First, deleting a book or member with loan history must fail: both foreign keys on loans have no cascade, so the rows survive as inventory and financial records. Deleting a loan, however, cascades to its fine, since a fine without its loan is meaningless. Second, nothing prevents a member from holding two copies of the same title at once — which may be a policy choice, not a bug — but the more serious gap is that loans does not verify returned_at is after borrowed_at; a CHECK constraint belongs there.

Three additions cover most library requirements. A reservations table (book_id, member_id, requested_at, expires_at) handles holds, can be ordered by requested_at when demand exceeds supply, and converts into a loan when a copy returns. A book_copies table, keyed under books with barcode UNIQUE, turns total_copies into a derived count and lets the library track damaged or lost items individually. Finally, a categories.parent_id self-reference mirrors the tree pattern from the e-commerce schema, so a broad shelf like Science can contain Physics and Biology without flattening the hierarchy. That keeps the first year of operation from forcing a rewrite.

The diagram

Tables (7)

Relationships (5)

• 1:Ncategories.id→books.category_id
• 1:Nbooks.id→loans.book_id
• 1:Nmembers.id→loans.member_id
• 1:1loans.id→fines.loan_idON DELETE CASCADE
• N:Mbooks.id→authors.id

The SQL

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(80) NOT NULL UNIQUE
);

CREATE TABLE authors (
  id SERIAL PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL,
  country VARCHAR(60)
);

CREATE TABLE books (
  id SERIAL PRIMARY KEY,
  category_id INTEGER REFERENCES categories(id),
  isbn VARCHAR(20) NOT NULL UNIQUE,
  title VARCHAR(200) NOT NULL,
  published_year INTEGER,
  total_copies INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE book_authors (
  book_id INTEGER NOT NULL REFERENCES books(id) ON DELETE CASCADE,
  author_id INTEGER NOT NULL REFERENCES authors(id) ON DELETE CASCADE,
  PRIMARY KEY (book_id, author_id)
);

CREATE TABLE members (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  full_name VARCHAR(120) NOT NULL,
  joined_at DATE NOT NULL DEFAULT CURRENT_DATE,
  status VARCHAR(20) NOT NULL DEFAULT 'active'
);

CREATE TABLE loans (
  id SERIAL PRIMARY KEY,
  book_id INTEGER NOT NULL REFERENCES books(id),
  member_id INTEGER NOT NULL REFERENCES members(id),
  borrowed_at TIMESTAMP NOT NULL DEFAULT now(),
  due_date DATE NOT NULL,
  returned_at TIMESTAMP
);

CREATE TABLE fines (
  id SERIAL PRIMARY KEY,
  loan_id INTEGER NOT NULL UNIQUE REFERENCES loans(id) ON DELETE CASCADE,
  amount DECIMAL(8,2) NOT NULL CHECK (amount > 0),
  reason VARCHAR(120) NOT NULL,
  issued_at TIMESTAMP NOT NULL DEFAULT now(),
  paid_at TIMESTAMP
);

Keep learning

• What Is an ER Diagram?
• How to Create an ER Diagram
• SQL to ER Diagram: The Complete Guide
• Primary Key vs Foreign Key
• All guides