How do you prevent a member from borrowing more books than their limit allows?

At borrow time, within a database transaction with a row lock on the member record, count their active loans (SELECT COUNT(*) FROM loans WHERE member_id = ? AND returned_at IS NULL). If the count equals or exceeds borrow_limit, raise an error. The row lock prevents two concurrent borrow requests from both passing the check and both being granted, which would exceed the limit.

How does the reservation queue work when a popular book is returned?

Reservations are fulfilled FIFO. When a copy is returned, the service queries for the oldest PENDING reservation for that book_id. If found: the copy status is set to RESERVED, the reservation status to READY, and a notification is sent to the member giving them 3 days to collect. If the member does not collect within 3 days, the reservation expires and the next in queue is notified. If no reservation exists, the copy becomes AVAILABLE.

How are overdue fines calculated and enforced?

On return, if returned_at.date() > due_date, calculate fine = overdue_days * daily_rate (e.g., $0.25/day). A Fine record is created linked to the Loan. The fine_amount on the Loan is also set for fast lookup. Before allowing future borrowing, the service checks if the member's total unpaid fines exceed the threshold (e.g., $10). If yes, borrowing is blocked until fines are paid. Fines can be waived by staff (waived_by field stores the staff user_id).

Why use Elasticsearch for library catalog search instead of SQL LIKE queries?

SQL LIKE queries (WHERE title LIKE '%query%') cannot use indexes and require full table scans — unacceptably slow for large catalogs. Elasticsearch uses an inverted index that maps tokens to documents, enabling full-text search in milliseconds across millions of books. It also supports relevance ranking (TF-IDF or BM25), typo tolerance (fuzzy matching), autocomplete (edge n-gram analyzer), and filtered search by genre and availability — none of which are practical in SQL.

How do you handle the renewal restriction when a book has reservations?

Before allowing a renewal, the service queries the Reservation table: does any PENDING reservation exist for this book_id? If yes, deny the renewal with a message explaining that another member is waiting. This is a business rule that ensures members cannot indefinitely extend loans on popular books while others wait. The query is simple and fast: SELECT COUNT(*) FROM reservations WHERE book_id = ? AND status = 'PENDING'.

Low-Level Design: Library Management System — Catalog, Borrowing, Reservations, and Fines

⏱ 6 min read

Core Entities

Book: book_id, isbn, title, authors (array), publisher, published_year, genre, language, description, cover_url. BookCopy: copy_id, book_id, library_id, condition (NEW, GOOD, FAIR, POOR), status (AVAILABLE, BORROWED, RESERVED, LOST, DAMAGED), acquisition_date, last_audited_at. Member: member_id, first_name, last_name, email, phone, address, type (STUDENT, FACULTY, PUBLIC, STAFF), membership_expiry, borrow_limit, status (ACTIVE, SUSPENDED, EXPIRED). Loan: loan_id, copy_id, member_id, borrowed_at, due_date, returned_at, renewal_count, fine_amount, fine_paid. Reservation: reservation_id, book_id, member_id, status (PENDING, READY, FULFILLED, EXPIRED, CANCELLED), requested_at, ready_at, expires_at. Fine: fine_id, loan_id, member_id, amount, reason (OVERDUE, DAMAGED, LOST), issued_at, paid_at, waived_by.

Borrowing and Return Flow

class LibraryService:
    LOAN_PERIODS = {'STUDENT': 14, 'FACULTY': 30, 'PUBLIC': 14, 'STAFF': 21}
    DAILY_FINE_RATE = 0.25  # $0.25 per day overdue
    MAX_RENEWALS = 2

    def borrow_book(self, member_id: int, copy_id: int) -> Loan:
        with self.db.transaction():
            member = self.repo.get_member(member_id, lock=True)
            if member.status != MemberStatus.ACTIVE:
                raise InactiveMemberError()
            if member.membership_expiry = member.borrow_limit:
                raise BorrowLimitExceededError(
                    f'Limit: {member.borrow_limit}, active: {active_loans}'
                )

            outstanding_fine = self.repo.get_outstanding_fine_total(member_id)
            if outstanding_fine > 10.00:
                raise OutstandingFinesError(f'Unpaid fines: ${outstanding_fine:.2f}')

            copy = self.repo.get_copy(copy_id, lock=True)
            if copy.status != CopyStatus.AVAILABLE:
                raise CopyNotAvailableError()

            due_date = date.today() + timedelta(days=self.LOAN_PERIODS[member.type])
            loan = Loan(copy_id=copy_id, member_id=member_id,
                        borrowed_at=datetime.utcnow(), due_date=due_date,
                        renewal_count=0)
            copy.status = CopyStatus.BORROWED
            self.repo.save(loan)
            self.repo.save(copy)
            return loan

    def return_book(self, loan_id: int) -> Loan:
        with self.db.transaction():
            loan = self.repo.get_loan(loan_id, lock=True)
            loan.returned_at = datetime.utcnow()
            copy = self.repo.get_copy(loan.copy_id)

            if loan.returned_at.date() > loan.due_date:
                overdue_days = (loan.returned_at.date() - loan.due_date).days
                loan.fine_amount = overdue_days * self.DAILY_FINE_RATE
                self.repo.create_fine(loan.loan_id, loan.member_id,
                                      loan.fine_amount, 'OVERDUE')

            # Check if anyone has a reservation for this book
            reservation = self.repo.get_next_reservation(copy.book_id)
            if reservation:
                copy.status = CopyStatus.RESERVED
                reservation.status = ReservationStatus.READY
                reservation.ready_at = datetime.utcnow()
                reservation.expires_at = datetime.utcnow() + timedelta(days=3)
                self.repo.save(reservation)
                self._notify_member(reservation.member_id, 'BOOK_READY')
            else:
                copy.status = CopyStatus.AVAILABLE
            return loan

Catalog Search and Discovery

Search requirements: full-text search across title, author, ISBN, subject. Filter by availability, genre, language, year range. Elasticsearch index on the Book table. Index fields: title (analyzed, boosted 2x), authors (analyzed), isbn (keyword), genre (keyword), language (keyword), published_year (integer). On book added/updated: sync to Elasticsearch via event-driven update (or near-real-time sync using Debezium CDC from PostgreSQL). Search query: multi-match across title and authors. Filter by genre and language. Sort by relevance (default), title (alphabetical), or year. Availability filter: a “has_available_copy” boolean on the Book index, updated when copy statuses change. Note: this is denormalized — updated asynchronously, may be slightly stale. For exact availability: query the BookCopy table directly. Autocomplete: edge n-gram analyzer on title and author fields for prefix-based suggestions as the user types. Return top 10 suggestions with book cover thumbnail URL. ISBN lookup: exact match, bypasses full-text search entirely.

Renewals, Reservations, and Reporting

Renewal: extend due_date by the standard loan period. Restrictions: cannot renew if the book has pending reservations (another member is waiting). Cannot renew if already renewed MAX_RENEWALS times. Cannot renew if the member has outstanding fines > $10. Each renewal increments renewal_count. Reservation flow: if no copies are available, member creates a reservation. Reservations are fulfilled FIFO per book. On copy return: system checks the reservation queue and assigns the copy to the next reservation. Member has 3 days to pick up before the reservation expires and the next in queue is notified. Automated jobs: OverdueReminderJob: daily, query loans where due_date = today + 2 days and not returned. Send reminder email. OverdueFineJob: daily, query loans where due_date < today and not returned. Compute accruing fine (daily_rate * overdue_days). Reservation expiry job: hourly, query reservations where status=READY and expires_at < now. Mark EXPIRED, release copy, notify next in queue. Reporting: books borrowed per month, most popular titles, overdue by member, fine revenue by category. Generated via SQL aggregates on the Loan and Fine tables. Exported to CSV or displayed in the admin dashboard.