Low Level Design: Library Management System

A library management system handles catalog browsing, checkouts, returns, reservations, and fines. This low level design covers the core schemas and flows that make those operations reliable at scale.

Catalog Schema

The catalog splits into two tables: one for the logical book record and one per physical copy.

Book Table

CREATE TABLE book (
  isbn         VARCHAR(13) PRIMARY KEY,
  title        VARCHAR(255) NOT NULL,
  author       VARCHAR(255) NOT NULL,
  genre        VARCHAR(100),
  total_copies INT NOT NULL DEFAULT 0
);

Book Copy Table

CREATE TABLE book_copy (
  copy_id  BIGINT PRIMARY KEY AUTO_INCREMENT,
  isbn     VARCHAR(13) NOT NULL REFERENCES book(isbn),
  status   ENUM('available','checked_out','reserved','lost') NOT NULL DEFAULT 'available',
  INDEX (isbn, status)
);

Member Schema

CREATE TABLE member (
  member_id      BIGINT PRIMARY KEY AUTO_INCREMENT,
  name           VARCHAR(255) NOT NULL,
  email          VARCHAR(255) UNIQUE NOT NULL,
  plan           ENUM('basic','premium') NOT NULL DEFAULT 'basic',
  active_checkouts INT NOT NULL DEFAULT 0,
  fines_owed     DECIMAL(8,2) NOT NULL DEFAULT 0.00
);

Basic plan caps active checkouts at 3; premium at 10. Members with unpaid fines over $5.00 are blocked from new checkouts.

Checkout Flow

Staff scans the member card (reads member_id) and the book barcode (reads copy_id).
System verifies the member is active, has no blocking fines, and is under their checkout limit.
System verifies the copy status is available.
A loan record is created with due_date = NOW() + interval 14 day (or 21 for premium).
The copy status is updated to checked_out and active_checkouts is incremented atomically inside a transaction.

CREATE TABLE loan (
  loan_id   BIGINT PRIMARY KEY AUTO_INCREMENT,
  copy_id   BIGINT NOT NULL REFERENCES book_copy(copy_id),
  member_id BIGINT NOT NULL REFERENCES member(member_id),
  checked_out_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  due_date       DATE NOT NULL,
  returned_at    DATETIME,
  fine_charged   DECIMAL(8,2) NOT NULL DEFAULT 0.00
);

Return Flow

Staff scans the book barcode.
System fetches the open loan record for that copy.
Fine is calculated: if CURDATE() > due_date, fine = days_overdue * 0.25.
Fine is added to member.fines_owed and recorded on the loan row.
Copy status is set back to available (or reserved if a reservation is pending — see below).
active_checkouts is decremented.

Reservation Queue

When all copies of an ISBN are checked out, a member can join the waitlist. The queue is FIFO per ISBN.

CREATE TABLE reservation (
  reservation_id BIGINT PRIMARY KEY AUTO_INCREMENT,
  isbn           VARCHAR(13) NOT NULL REFERENCES book(isbn),
  member_id      BIGINT NOT NULL REFERENCES member(member_id),
  reserved_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  notified_at    DATETIME,
  expires_at     DATETIME,
  status         ENUM('waiting','notified','fulfilled','cancelled') NOT NULL DEFAULT 'waiting',
  INDEX (isbn, status, reserved_at)
);

On return, after the copy is marked available, the system queries for the earliest waiting reservation for that ISBN. If found, the copy is immediately set to reserved, the reservation status becomes notified, expires_at is set to 48 hours out, and an email is sent to the member. If the member does not pick up within 48 hours, the reservation expires and the next in queue is notified.

Fine Payment

Payment decrements member.fines_owed and records a row in a payment table with amount, method, and timestamp. Partial payments are allowed; the blocking threshold is re-evaluated after each payment.

Search

Basic search uses FULLTEXT indexes on book.title, book.author, and book.genre. ISBN lookup is an exact match on the primary key. For keyword search across all fields, a FULLTEXT index spans title, author, and genre. For production scale, an Elasticsearch index mirrors the book table and supports faceted filtering by genre, availability, and author.

Barcode Integration

Each physical copy has a barcode encoding its copy_id. Scanners at checkout and return desks POST the decoded copy_id to a backend endpoint. The ISBN is derived by joining to book_copy. Member cards carry a barcode of the member_id. Self-service kiosks follow the same API as staff terminals — the checkout and return endpoints are stateless and auth-gated by staff token or member PIN.

Key Design Decisions

Splitting book and book_copy keeps copy-level status updates from locking the catalog row.
All checkout/return mutations run inside a single DB transaction to keep counters consistent.
FIFO reservation queue uses reserved_at ordering with a covering index so the next-in-line lookup is O(log n).
Fine calculation is done at return time, not via a nightly job, so the amount is always accurate to the day.

Frequently Asked Questions: Library Management System

What are the core entities in a library management system?

The primary entities are: Book (ISBN, title, author list, genre, publisher, year) — represents the catalog record for a title, not a physical copy. BookItem (barcode, condition, location code, status: available / checked_out / reserved / lost) — represents one physical copy of a Book. Member (member_id, name, contact info, membership type, active_loans count, fine_balance) — a registered library patron. Librarian (staff account with elevated permissions for catalog management and fine waiving). Loan (loan_id, member_id, book_item_id, checkout_date, due_date, return_date, status) — the checkout record. Reservation (reservation_id, member_id, book_id, reserved_at, status: waiting / fulfilled / cancelled) — a hold request for a title when all copies are out. Fine (fine_id, loan_id, amount, paid_at) — overdue penalty record. Supporting entities: Catalog (search index over Books), NotificationService (alerts for due dates, reservation availability), and PaymentService (fine collection).

How does the book checkout and return flow work?

Checkout flow: (1) Librarian or self-serve kiosk scans the member’s card and the book item’s barcode. (2) System checks member eligibility: account active, no unpaid fines above threshold, not at the maximum concurrent loan limit (e.g., 5 books). (3) System checks the BookItem status is “available” — atomically update status to “checked_out” (optimistic lock to prevent race with another simultaneous checkout). (4) Create a Loan record with due_date = today + loan_period (e.g., 14 days for standard membership). (5) Decrement available copy count on the Book record. (6) Send confirmation to member. Return flow: (1) Scan barcode at return desk or drop-box. (2) Look up the open Loan by book_item_id. (3) Calculate overdue days: if return_date > due_date, compute fine = overdue_days * daily_rate; create a Fine record linked to the Loan. (4) Set Loan.status = returned, set BookItem.status = available (or “to-be-shelved”). (5) Check if any Reservation exists for this Book — if yes, notify the first waiting member and set BookItem.status = reserved for their pickup window.

How does the reservation queue work when all copies are checked out?

The reservation queue is a FIFO queue per Book title (not per BookItem). When a member requests a hold on a title with zero available copies: create a Reservation record with status = waiting and position = current queue length + 1. When any copy of that title is returned: the return flow queries for the earliest waiting Reservation (lowest position or earliest reserved_at). The matched Reservation transitions to status = notified; the member is sent a pickup notification with a deadline (e.g., 3 days to collect). The BookItem is set to status = reserved and associated with that Reservation. If the member picks up within the deadline, the Reservation becomes fulfilled and a normal Loan is created. If they miss the deadline, the Reservation is cancelled, the BookItem returns to available, and the next member in queue is notified. Members can cancel their own reservation at any time, which triggers a queue compaction (re-number positions or rely on reserved_at ordering). Limit the number of simultaneous active reservations per member (e.g., 3) to prevent queue squatting.

How do you calculate and enforce overdue fines?

Fine calculation: fine_amount = max(0, (return_date – due_date).days) * daily_rate, where daily_rate is configurable per membership tier or book type (e.g., reference books have a higher rate). Apply a maximum fine cap per item (e.g., $10 or the replacement cost of the book, whichever is lower) so the fine never exceeds the value of the item. For books not yet returned: compute an accruing fine = (today – due_date).days * daily_rate, updated nightly by a batch job. Enforcement: block new checkouts when a member’s total outstanding fine_balance exceeds a threshold (e.g., $5). Block renewals on the overdue item itself. Send automated reminder notifications: 2 days before due, on the due date, and at 3-day intervals while overdue. For long-overdue items (e.g., 60+ days), mark the BookItem as “assumed lost,” charge the member the replacement cost, and remove the item from available inventory. Fine payment is recorded in the Fine table (paid_at timestamp, payment_method); the member’s fine_balance is decremented accordingly and checkout eligibility is immediately restored if below the threshold.