DiagramDB
DiagramDB / Library Management System ERD

ER Diagram for Library Management System

A library catalogues books (titles) and the physical copies on its shelves, lends copies to members, takes reservations and charges fines. Separating a book from its copies is the key design decision.

All examples
Drawing 9 tables…

Why each relationship is drawn this way

  • books ↔ authorsMany-to-many: a book can have several authors and an author writes many books, so book_authors is a junction table with a composite primary key.
  • book → copiesOne title, many physical copies. Loans point at a copy (the thing on the shelf), reservations point at a book (any copy will do).
  • member → loansA member borrows many copies over time; a loan with no returned_on is still out.
  • loan → fineloan_id is UNIQUE on fines, so the diagram draws it one-to-one: a loan has at most one fine.
  • publisher → bookspublisher_id is nullable for self-published or unknown-publisher titles.

The schema

CREATE TABLE authors (
  id INT PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL
);
CREATE TABLE publishers (
  id INT PRIMARY KEY,
  name VARCHAR(120) NOT NULL
);
CREATE TABLE books (
  id INT PRIMARY KEY,
  isbn VARCHAR(13) NOT NULL UNIQUE,
  title VARCHAR(200) NOT NULL,
  publisher_id INT REFERENCES publishers(id),
  published_year INT
);
CREATE TABLE book_authors (
  book_id INT NOT NULL,
  author_id INT NOT NULL,
  PRIMARY KEY (book_id, author_id),
  FOREIGN KEY (book_id) REFERENCES books(id),
  FOREIGN KEY (author_id) REFERENCES authors(id)
);
CREATE TABLE copies (
  id INT PRIMARY KEY,
  book_id INT NOT NULL REFERENCES books(id),
  barcode VARCHAR(30) NOT NULL UNIQUE,
  shelf VARCHAR(20)
);
CREATE TABLE members (
  id INT PRIMARY KEY,
  card_number VARCHAR(20) NOT NULL UNIQUE,
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(255),
  joined_on DATE NOT NULL
);
CREATE TABLE loans (
  id INT PRIMARY KEY,
  copy_id INT NOT NULL REFERENCES copies(id),
  member_id INT NOT NULL REFERENCES members(id),
  borrowed_on DATE NOT NULL,
  due_on DATE NOT NULL,
  returned_on DATE
);
CREATE TABLE reservations (
  id INT PRIMARY KEY,
  book_id INT NOT NULL REFERENCES books(id),
  member_id INT NOT NULL REFERENCES members(id),
  reserved_at TIMESTAMP NOT NULL
);
CREATE TABLE fines (
  id INT PRIMARY KEY,
  loan_id INT NOT NULL UNIQUE REFERENCES loans(id),
  amount_cents INT NOT NULL,
  paid_on DATE
);

Design notes

  • Overdue loans: WHERE returned_on IS NULL AND due_on < CURRENT_DATE.
  • Use the ISBN as a unique column, not the primary key; old books and pamphlets have none.

Tables at a glance

  • authors: id, full_name
  • publishers: id, name
  • books: id, isbn, title, publisher_id, published_year
  • book_authors: book_id, author_id
  • copies: id, book_id, barcode, shelf
  • members: id, card_number, full_name, email, joined_on
  • loans: id, copy_id, member_id, borrowed_on, due_on, returned_on
  • reservations: id, book_id, member_id, reserved_at
  • fines: id, loan_id, amount_cents, paid_on

Questions

What are the entities in a library management system ER diagram?

This design uses 9 tables: authors, publishers, books, book_authors, copies, members, loans, reservations, fines. Many-to-many: a book can have several authors and an author writes many books, so book_authors is a junction table with a composite primary key.

How many relationships does the library management system ER diagram have?

9 foreign-key relationships. The many-to-many ones are resolved with junction tables, which is how they must be stored in a relational database.

Can I edit this ER diagram and export it?

Yes. “Edit in the ER diagram generator” opens the same SQL in the editor, where you can add tables, drag boxes and export SVG, PNG, Mermaid, DBML or SQL for PostgreSQL, MySQL, SQL Server or SQLite.

Which notation is used?

Crow’s foot: a fork means many, a bar means exactly one and a circle means optional (zero). A nullable foreign key gets the circle on the parent end.

More ER diagram examples