Design a Library Management System (API and Schema)

Design the API and database schema for a **library management system**. The library lends physical books to registered members. Members can search the catalog, borrow available copies, return them, and reserve (place a hold on) a title when every copy is currently checked out. Librarians (staff) add or remove books and copies, manage members, and collect late fees. Your design should make it impossible to lend the same physical copy to two members at once and should correctly track who currently holds each copy and what is due when. Walk through the **data model** (tables, columns, types, keys, and relationships), the **REST API** (the core endpoints with their request/response shapes), and the **read and write paths** for the central operations: search, borrow, return, and reserve. Be explicit about how borrow handles concurrency so a copy is never double-lent, and how the system enforces a borrowing limit and computes overdue/late fees. ```hint Model the right grain Separate the abstract *title* (a `book`, identified by ISBN) from the *physical thing on the shelf* (a `book_copy` with its own barcode). Loans, holds, and availability all hinge on individual copies, not titles. Drawing this line first makes "5 copies, 3 checked out" and "lend this exact copy" both fall out naturally. ``` ```hint The borrow race Two members clicking "Borrow" on the last available copy at the same instant is the crux. Think about which single row both transactions must contend for, and how `SELECT ... FOR UPDATE` (or a conditional `UPDATE ... WHERE status = 'AVAILABLE'`) lets exactly one win without a global lock. ``` ```hint Holds and overdue A hold is just a FIFO queue per title. When a copy is returned, decide whether it satisfies the head of that queue or goes back on the shelf. Overdue isn't a stored boolean — derive it from `due_date < now()` so it's always correct, and snapshot the fee only when the loan actually closes. ``` ### Constraints & Assumptions - Catalog on the order of $10^5$–$10^6$ titles and a few million physical copies; membership on the order of $10^5$ members. Read-heavy (search and browse dominate writes). - A single library branch (you may note how you would extend to multiple branches, but do not need to fully design it). - Borrowing rules: a member may hold at most $N$ active loans at once (assume $N = 5$); a standard loan period is 21 days; late fee accrues per day overdue up to a cap. - Each physical copy can be on loan to at most one member at any time. - Reservations (holds) are FIFO per title and expire if not picked up within a set window after a copy becomes available. - Strong consistency is required for borrow/return (no double-lending, no negative availability). Search may be eventually consistent. ### Clarifying Questions to Ask - Is this a single branch, or do copies live at multiple physical branches with inter-branch transfers? (Affects whether `book_copy` needs a `branch_id` and whether availability is per-branch.) - What are the exact lending policies — loan period, max simultaneous loans, renewal rules, and the late-fee formula and cap? - Do members reserve a *title* (any copy) or a *specific copy*? Is the hold queue FIFO, and what is the pickup-expiry window? - What is the expected catalog size and request volume, and is search a prefix/keyword search over title/author or full-text? Can search be served by a separate index? - Are payments (late fees) settled inside this system or handed off to a billing service? Do we need an audit/history of every loan for reporting? - What authentication/authorization roles exist (member vs. librarian/admin), and which endpoints are staff-only? ### What a Strong Answer Covers ```premium-lock What a Strong Answer Covers ``` ### Follow-up Questions - How would you extend the schema and borrow logic to support **multiple branches**, where a member can borrow a copy from any branch and return it to a different one? - A member complains they were charged a late fee for a book they returned on time. How does your design let you **audit and prove** the loan history, and what would you store to make this dispute-resolvable? - The "available copy count" shown in search is slightly stale under heavy load. Where exactly does that staleness come from in your design, and how would you keep the borrow path correct even when the displayed count is wrong? - How would you implement **renewals** (extending a loan) such that a renewal is rejected if another member already has a hold on that title?