Katkat
Back

Design a Library Management System (API and Schema)

AmazonSoftware Engineer · Technical Screen
HardSoftware EngineerTechnical ScreenSystem Design

Last updated

This problem evaluates your ability to design a schema and API for a transactional library system, focusing on handling concurrent checkouts and maintaining data integrity in a read-heavy environment.

Scenario

You are tasked with designing the backend system for a modern library management service. The system must support thousands of members browsing millions of books and performing concurrent checkouts.

Requirements

  1. Search/Browse: Users can search for books by title, author, or ISBN.
  2. Borrowing: A user can check out a book if at least one physical copy is available. A single book title may have multiple physical copies (assets) associated with it.
  3. Return: A user can return a book, making it available for others.
  4. Concurrency: Ensure that two users cannot check out the last available physical copy of a book simultaneously.

Tasks

  1. Data Schema: Design the relational database schema. Define the entities (e.g., Book, BookCopy, Member, Loan) and the relationships between them. Highlight key indexes for performance.
  2. API Design: Define the REST API endpoints for searching books, checking out a book, and returning a book. Include necessary parameters and HTTP status codes.
  3. Concurrency Strategy: Explain how you would prevent race conditions during the checkout process.

Constraints

  • Assume read-heavy traffic (100:1 read-to-write ratio).
  • Maintain strict consistency for inventory count to prevent over-lending.

Hints

Hint 1

Consider separating the concept of a 'Book' (catalog metadata) from a 'BookCopy' (individual trackable asset).

Hint 2

Use database transactions with appropriate isolation levels or optimistic locking on the inventory record to handle concurrent checkouts.

Hint 3

For search performance, think about how to structure your indexing strategy for title or author lookups without sacrificing write throughput on the loans table.

Follow-up questions

  • How would you modify the system to handle 'Reservations' or 'Waitlists' if no copies are available?
  • How would you scale the database if the search functionality becomes too slow due to the high volume of incoming traffic?
  • What steps would you take to ensure the system remains available even if the database experiences a brief connectivity issue?