Multi-Branch Library Management System

0) Problem Restatement

Design a library system with several branches (asked at Amazon twice, focused on schema and APIs). Members search the catalog, see which branches have available copies, check out and return books, and place holds (reservations) on books that are all checked out. When a copy comes back, the next person in the hold queue is notified. The system tracks due dates and late fines.

1) Requirements

• Catalog search by title, author, ISBN and subject, with availability per branch.
• Checkout (the member has a limit, e.g., 5 books) and return (at any branch).
• Holds: join a queue for a title. When a copy is ready, notify the member and keep it for them for 3 days.
• Renewals (if nobody is waiting), due-date reminders, fines for late returns.
• Librarian functions: add copies, mark lost or damaged, and transfer between branches.

2) Data Model

CREATE TABLE books    (book_id BIGINT PRIMARY KEY, isbn TEXT UNIQUE, title TEXT, authors TEXT[], subjects TEXT[]);
CREATE TABLE branches (branch_id INT PRIMARY KEY, name TEXT, address TEXT);
CREATE TABLE copies (
  copy_id BIGINT PRIMARY KEY, book_id BIGINT REFERENCES books, branch_id INT REFERENCES branches,
  status TEXT,          -- available, on_loan, on_hold_shelf, in_transit, lost
  version INT DEFAULT 0
);
CREATE TABLE members (member_id BIGINT PRIMARY KEY, name TEXT, email TEXT, max_loans INT DEFAULT 5, blocked BOOLEAN);
CREATE TABLE loans (
  loan_id BIGINT PRIMARY KEY, copy_id BIGINT, member_id BIGINT,
  checked_out_at TIMESTAMP, due_at TIMESTAMP, returned_at TIMESTAMP, renewals INT DEFAULT 0
);
CREATE TABLE holds (
  hold_id BIGINT PRIMARY KEY, book_id BIGINT, member_id BIGINT, pickup_branch_id INT,
  created_at TIMESTAMP,   -- queue order
  status TEXT,            -- waiting, ready, fulfilled, expired, cancelled
  copy_id BIGINT, ready_until TIMESTAMP
);
CREATE TABLE fines (fine_id BIGINT PRIMARY KEY, member_id BIGINT, loan_id BIGINT, amount_cents INT, paid BOOLEAN);

CREATE INDEX ON copies (book_id, branch_id, status);
CREATE INDEX ON holds (book_id, status, created_at);
CREATE UNIQUE INDEX one_active_loan_per_copy ON loans (copy_id) WHERE returned_at IS NULL;

Key idea: books (titles) are separate from copies (physical items). Availability is about copies, and holds are placed on the book (any copy).

3) APIs

• GET /v1/books?q=&branch_id= → books with available_copies per branch
• POST /v1/loans { copy_id, member_id } → checkout
• POST /v1/loans/{id}/return { branch_id }
• POST /v1/loans/{id}/renew
• POST /v1/holds { book_id, member_id, pickup_branch_id } → { position_in_queue }
• DELETE /v1/holds/{id}

4) Architecture

flowchart LR
    M["Members - web/app"] --> API["Library API"]
    L["Librarian desk"] --> API
    API --> DB[("Relational DB")]
    API --> SI[("Search index - catalog")]
    API --> K[("Events: returned, hold ready")]
    K --> N["Notifications - email/SMS"]
    JOB["Daily jobs: reminders, fines, hold expiry"] --> DB
    JOB --> N

5) Key Flows

5.1 Checkout (with concurrency)

In one transaction:

1. Check the member isn't blocked and has fewer than max_loans.
2. Change the copy status only if it's available: UPDATE copies SET status='on_loan', version=version+1 WHERE copy_id=? AND status='available'. If 0 rows are updated, someone else took it, so return an error.
3. Insert the loan with due_at = now + 21 days. The unique index on active loans per copy is a second safety net.

5.2 Return and holds

1. Mark the loan returned, and compute a fine if late.
2. Is anyone waiting for this book? Find the oldest waiting hold (ORDER BY created_at LIMIT 1 FOR UPDATE).

• If yes: assign this copy to the hold, set the hold ready with ready_until = now + 3 days, and notify the member. If the pickup branch is different, set the copy in_transit first.
• If no: set the copy available at the branch where it was returned.

5.3 Renew

Allowed if renewals < 2, the loan isn't overdue (policy), and no waiting holds exist for that book.

6) Trade-offs & Notes

• Book vs copy modeling makes branch availability and holds simple.
• Conditional updates (or SELECT ... FOR UPDATE) prevent two checkouts of the same copy.
• Search uses a text index (Postgres full-text or Elasticsearch) with availability counts joined or cached per branch.
• Fines are computed at return (days late × daily rate, capped), and unpaid fines above a threshold block new loans.
• E-books: model licenses (N concurrent loans per license) instead of physical copies, with the same loan and hold logic.

7) Wrap-Up

Separate books (titles) from copies (physical items at branches), and model loans, holds (a queue ordered by time) and fines in a relational schema with the right indexes. Check out with a conditional status update inside a transaction so a copy is never lent twice. On return, hand the copy to the oldest waiting hold (possibly via transfer) or make it available, and run daily jobs for reminders, fines and hold expiry.