Skip to content
notes.vinhnq.com

Search

Database & ORMMid24 min read

Databases: designing and querying a library system (exercise)

Review 17 cards
On this page

Databases series: 1. Tables and basic SQL · 2. ERD, joins, GROUP BY · 3. Normalization · 4. Library exercise · 5. Indexes and query performance · 6. Transactions and locking

One big exercise that practises the first three posts of the series: tables and basic SQL, ERD, joins, GROUP BY and normalization. It follows the order of real work: read the brief, fix what’s missing, draw the ERD, create constrained tables, load sample data, write 30 queries, then see what a senior would add before production. Dialect: MySQL 8; every query was run on MySQL 8.4.

1. The brief

Key idea: a library tracks members, staff, publishers, books and loan slips. Each borrowing is one slip, issued by one staff member.

Rules:

  • A member can borrow many books. At any given time, a book is in the hands of only one member.
  • Each slip is issued by exactly one staff member; a staff member issues many slips.
  • A publisher publishes many books; each book has exactly one publisher.
Detailed attributes and constraints from the brief
  • Member: card number (string ≤ 5 chars, key), surname, given name (≤ 15), address (≤ 50), date of birth, gender (M/F only), phone (≤ 15); all required.
  • Staff: auto-increment id, surname, given name, date of birth (required), hire date.
  • Book: id (≤ 5 chars), genre (one of: novel, historical, for kids, poems, crime story, science fiction, science), price (decimal, 3 decimal places), title (required).
  • Publisher: auto-increment id, name, city (≤ 50), phone (≤ 15); all required.
  • Loan slip: auto-increment number, date out, due date (date out must be before due date), penalty (decimal, 3 decimal places, non-negative, default 0).

2. Analyse and fix the brief before writing code

Key idea: check that the model can actually answer the business questions. Here the brief has no return date, and the reference solution misreads the “one book, one member” rule.

  1. No actual return date. The brief asks about “books not yet returned” and “slips that weren’t late”, but nothing records whether a book came back. I add returned_at DATETIME NULL (NULL = not returned yet).
  2. “A book can only be borrowed by one member”. The reference solution uses UNIQUE(book_id), so each book can only ever be borrowed once in its lifetime. The real rule: at any time there’s at most one open loan per book.
  1. The “Book 1-1 Loan” relationship in the reference solution therefore becomes Book 1-n Loan (over time), with at most one open loan.
  2. Date out and due date should be NOT NULL. The reference solution leaves them nullable and is missing the CHECK (penalty >= 0) the brief asks for.
  3. Book genre: CHECK ... IN (...) works (MySQL ≥ 8.0.16). If genres change often, a book_type lookup table with an FK is better, because adding a genre then doesn’t need an ALTER TABLE.

3. ERD

Key idea: four 1-n relationships. Members, staff and books all point to loan slips; publishers point to books.

erDiagram
  MEMBER    ||--o{ LOAN : borrows
  EMPLOYEE  ||--o{ LOAN : issues
  BOOK      ||--o{ LOAN : "is lent in"
  PUBLISHER ||--o{ BOOK : publishes
  MEMBER {
    string card_no PK
    string surname
    string given_name
    string address
    date birth_date
    char gender
    string phone
  }
  EMPLOYEE {
    int emp_id PK
    string surname
    string given_name
    date birth_date
    datetime hired_at
  }
  PUBLISHER {
    int pub_id PK
    string name
    string city
    string phone
  }
  BOOK {
    string book_id PK
    string type
    decimal price
    string title
    int pub_id FK
  }
  LOAN {
    int invoice_no PK
    string card_no FK
    int emp_id FK
    string book_id FK
    datetime date_out
    datetime due_date
    datetime returned_at
    decimal penalty
  }

4. Creating the tables

Key idea: put every rule of the brief into constraints (NOT NULL, CHECK, FK, UNIQUE) so the database keeps the data correct no matter who writes it.

CREATE TABLE member (
  card_no    VARCHAR(5)  PRIMARY KEY,
  surname    VARCHAR(15) NOT NULL,
  given_name VARCHAR(15) NOT NULL,
  address    VARCHAR(50) NOT NULL,
  birth_date DATE        NOT NULL,
  gender     CHAR(1)     NOT NULL CHECK (gender IN ('M', 'F')),
  phone      VARCHAR(15) NOT NULL
);

CREATE TABLE employee (
  emp_id     INT AUTO_INCREMENT PRIMARY KEY,
  surname    VARCHAR(15) NOT NULL,
  given_name VARCHAR(15) NOT NULL,
  birth_date DATE        NOT NULL,
  hired_at   DATETIME
);

CREATE TABLE publisher (
  pub_id INT AUTO_INCREMENT PRIMARY KEY,
  name   VARCHAR(50) NOT NULL,
  city   VARCHAR(50) NOT NULL,
  phone  VARCHAR(15) NOT NULL
);

CREATE TABLE book (
  book_id VARCHAR(5)    PRIMARY KEY,
  type    VARCHAR(20)   NOT NULL CHECK (type IN ('novel', 'historical', 'for kids', 'poems',
                                                 'crime story', 'science fiction', 'science')),
  price   DECIMAL(10,3) NOT NULL CHECK (price >= 0),
  title   VARCHAR(255)  NOT NULL,
  pub_id  INT           NOT NULL,
  CONSTRAINT fk_book_publisher FOREIGN KEY (pub_id) REFERENCES publisher(pub_id)
);

CREATE TABLE loan (
  invoice_no   INT AUTO_INCREMENT PRIMARY KEY,
  card_no      VARCHAR(5)    NOT NULL,
  emp_id       INT           NOT NULL,
  book_id      VARCHAR(5)    NOT NULL,
  date_out     DATETIME      NOT NULL,
  due_date     DATETIME      NOT NULL,
  returned_at  DATETIME      NULL,
  penalty      DECIMAL(10,3) NOT NULL DEFAULT 0 CHECK (penalty >= 0),
  -- only set while the loan is open -> the unique key only applies to open loans
  open_book_id VARCHAR(5) AS (CASE WHEN returned_at IS NULL THEN book_id END) STORED,
  CONSTRAINT chk_loan_dates CHECK (date_out < due_date),
  CONSTRAINT uq_loan_one_open_per_book UNIQUE (open_book_id),
  CONSTRAINT fk_loan_member   FOREIGN KEY (card_no) REFERENCES member(card_no),
  CONSTRAINT fk_loan_employee FOREIGN KEY (emp_id)  REFERENCES employee(emp_id),
  CONSTRAINT fk_loan_book     FOREIGN KEY (book_id) REFERENCES book(book_id),
  KEY idx_loan_card (card_no),
  KEY idx_loan_book (book_id),
  KEY idx_loan_emp_date (emp_id, date_out)
);

5. Sample data

Key idea: loan dates are relative to NOW(), so “overdue” and “today” stay meaningful whenever you run it.

The data covers every case: returned on time, returned late, overdue, not yet due, borrowed today. M0004 has never borrowed anything; “Little Lantern” at 8.5 is currently on loan.

INSERT INTO member VALUES
 ('M0001', 'Vu',   'Minh Anh',  '12 Hang Bai, Hanoi',  '1996-02-14', 'F', '0901000001'),
 ('M0002', 'Dang', 'Quoc Bao',  '45 Le Loi, Hue',      '1991-08-30', 'M', '0901000002'),
 ('M0003', 'Ngo',  'Thanh Tam', '7 Tran Phu, Da Nang', '2000-12-01', 'M', '0901000003'),
 ('M0004', 'Ly',   'Thu Ha',    '88 Nguyen Hue, HCMC', '1989-05-19', 'F', '0901000004');

INSERT INTO employee (surname, given_name, birth_date, hired_at) VALUES
 ('Bui', 'Duc Long', '1990-03-03', '2023-01-09 08:00:00'),
 ('Mai', 'Ngoc Lan', '1995-10-21', '2024-06-01 08:00:00'),
 ('Cao', 'Van Khoa', '1987-07-07', '2022-02-14 08:00:00');

INSERT INTO publisher (name, city, phone) VALUES
 ('Red River Books',   'Hanoi',            '0241000001'),
 ('Southern Press',    'Ho Chi Minh City', '0281000002'),
 ('Central Coast Pub', 'Da Nang',          '0236100003'),
 ('Tiny Shelf',        'Hanoi',            '0241000004');

INSERT INTO book VALUES
 ('B0001', 'novel',           21.500, 'The Quiet Harbor',       1),
 ('B0002', 'historical',      18.000, 'Dynasties of the Delta', 2),
 ('B0003', 'crime story',     24.900, 'The Last Alibi',         1),
 ('B0004', 'crime story',     19.750, 'Cold Ledger',            3),
 ('B0005', 'science fiction', 27.000, 'Orbit of Glass',         2),
 ('B0006', 'for kids',         8.500, 'Little Lantern',         4),
 ('B0007', 'science',         32.000, 'How Engines Think',      1),
 ('B0008', 'poems',           12.250, 'Monsoon Lines',          3),
 ('B0009', 'novel',           16.000, 'Paper Kites',            1);

INSERT INTO loan (card_no, emp_id, book_id, date_out, due_date, returned_at) VALUES
 ('M0001', 1, 'B0003', NOW() - INTERVAL 60 DAY, NOW() - INTERVAL 46 DAY, NOW() - INTERVAL 50 DAY), -- returned on time
 ('M0001', 2, 'B0004', NOW() - INTERVAL 40 DAY, NOW() - INTERVAL 26 DAY, NOW() - INTERVAL 20 DAY), -- returned late
 ('M0001', 1, 'B0001', NOW() - INTERVAL 20 DAY, NOW() - INTERVAL 6 DAY,  NULL),                    -- 6 days overdue
 ('M0002', 3, 'B0005', NOW() - INTERVAL 30 DAY, NOW() - INTERVAL 16 DAY, NULL),                    -- 16 days overdue
 ('M0002', 1, 'B0002', NOW() - INTERVAL 10 DAY, NOW() + INTERVAL 4 DAY,  NULL),                    -- out, not yet due
 ('M0003', 2, 'B0003', NOW() - INTERVAL 1 HOUR, NOW() + INTERVAL 14 DAY, NULL),                    -- borrowed today
 ('M0003', 2, 'B0006', NOW() - INTERVAL 2 HOUR, NOW() + INTERVAL 14 DAY, NULL);                    -- borrowed today

6. The 30 queries

Key idea: write each query yourself first, then open the solutions to compare. Most of it is plain SELECT, JOIN and GROUP BY practice; what’s worth remembering is a handful of traps around NULLs, zero counts, indexes and dates.

The original brief has 30 requirements, but the answer key only covers the first 15, and its answer to #15 filters on the wrong member (the question asks for M0002, the answer uses M0001). The solutions below are mine, for all 30.

  1. All book details.
  2. Name and address of all male members.
  3. Total price of all books.
  4. Staff names and birth dates, oldest first.
  5. Title and price of novel books, most expensive first.
  6. Number of publishers.
  7. Title and due date of loans that are currently overdue.
  8. Number of books per genre.
  9. All details of books borrowed by member M0001.
  10. Set the price of every historical book to 25.99.
  11. Delete books priced under 10.
  12. Title and date out of science fiction books that were borrowed.
  13. Average book price.
  14. Title and publisher of books whose title starts with “The”.
  15. Title and date out of books borrowed by M0002 that cost more than 20.
  16. Books and their publisher, only books with at least one loan.
  17. Number of books per publisher.
  18. Title and publisher of books priced above the average.
  19. Every member and how many loans they’ve had.
  20. Title and publisher of the most and least expensive books.
  21. Total number of books published by the publishers.
  22. Books and their publisher that have at least one loan returned on time.
  23. Members who have borrowed books, with their loan count, highest first.
  24. Books per publisher, only publishers with at least two books.
  25. Members who borrowed crime story books at least twice.
  26. Books currently out, their date out, and how many days overdue.
  27. Title and publisher of books still out and more than 7 days overdue.
  28. How many slips each staff member issued today.
  29. Books with at least one loan whose publisher is in Hanoi, with their loan count.
  30. Books cheaper than the overall average price and published in Hanoi.

What’s most worth remembering from the solutions:

  • #7, #26, #27: always include returned_at IS NULL, or books returned long ago still count as overdue (the bug in the reference answer, since the original model has no return-date column).
  • #17, #19, #28: LEFT JOIN + COUNT(right-table column) so rows without loans still show 0.
  • #21, #22, #30: the brief is ambiguous, so pick a reading and say so (or ask).
Solutions to queries 1–10

1. All book details.

SELECT * FROM book;

In real code, list the columns instead of *: you avoid pulling unused columns over the network, and adding a column to the table won’t break your mapping.

2. Name and address of all male members.

SELECT CONCAT(surname, ' ', given_name) AS full_name, address
FROM member
WHERE gender = 'M';

3. Total price of all books.

SELECT SUM(price) AS total_price FROM book;

4. Staff names and birth dates, oldest first.

SELECT surname, given_name, birth_date
FROM employee
ORDER BY birth_date ASC;

5. Title and price of novel books, most expensive first.

SELECT title, price
FROM book
WHERE type = 'novel'
ORDER BY price DESC;

6. Number of publishers.

SELECT COUNT(*) AS publisher_count FROM publisher;

7. Title and due date of loans that are currently overdue.

SELECT b.title, l.due_date
FROM loan l
JOIN book b ON b.book_id = l.book_id
WHERE l.returned_at IS NULL
  AND l.due_date < NOW();

Without returned_at IS NULL, books returned long ago still count as overdue.

8. Number of books per genre.

SELECT type, COUNT(*) AS book_count
FROM book
GROUP BY type
ORDER BY book_count DESC;

9. All details of books borrowed by member M0001.

SELECT DISTINCT b.*
FROM loan l
JOIN book b ON b.book_id = l.book_id
WHERE l.card_no = 'M0001';

DISTINCT because a member may borrow the same book more than once. WHERE b.book_id IN (SELECT book_id FROM loan WHERE card_no = 'M0001') is also correct and de-duplicates naturally.

10. Set the price of every historical book to 25.99.

UPDATE book SET price = 25.990 WHERE type = 'historical';
Solutions to queries 11–20

11. Delete books priced under 10.

DELETE FROM book
WHERE price < 10
  AND NOT EXISTS (SELECT 1 FROM loan l WHERE l.book_id = book.book_id);

Why the NOT EXISTS is needed: see the “Deleting rows that have history” block above.

12. Title and date out of science fiction books that were borrowed.

SELECT b.title, l.date_out
FROM loan l
JOIN book b ON b.book_id = l.book_id
WHERE b.type = 'science fiction';

13. Average book price.

SELECT ROUND(AVG(price), 3) AS avg_price FROM book;

14. Title and publisher of books whose title starts with “The”.

SELECT b.title, p.name AS publisher
FROM book b
JOIN publisher p ON p.pub_id = b.pub_id
WHERE b.title LIKE 'The%';

15. Title and date out of books borrowed by M0002 that cost more than 20.

SELECT b.title, l.date_out
FROM loan l
JOIN book b ON b.book_id = l.book_id
WHERE l.card_no = 'M0002'
  AND b.price > 20;

16. Books and their publisher, only books with at least one loan.

SELECT b.book_id, b.title, p.name AS publisher
FROM book b
JOIN publisher p ON p.pub_id = b.pub_id
WHERE EXISTS (SELECT 1 FROM loan l WHERE l.book_id = b.book_id);

17. Number of books per publisher.

SELECT p.pub_id, p.name, COUNT(b.book_id) AS book_count
FROM publisher p
LEFT JOIN book b ON b.pub_id = p.pub_id
GROUP BY p.pub_id, p.name;

LEFT JOIN so publishers with no books still show up with 0.

18. Title and publisher of books priced above the average.

SELECT b.title, p.name AS publisher, b.price
FROM book b
JOIN publisher p ON p.pub_id = b.pub_id
WHERE b.price > (SELECT AVG(price) FROM book);

A non-correlated subquery (one that doesn’t depend on the outer row) runs only once.

19. Every member and how many loans they’ve had.

SELECT m.card_no, m.surname, m.given_name, COUNT(l.invoice_no) AS loan_count
FROM member m
LEFT JOIN loan l ON l.card_no = m.card_no
GROUP BY m.card_no, m.surname, m.given_name;

M0004 has never borrowed anything and shows up with 0.

20. Title and publisher of the most and least expensive books.

SELECT b.title, p.name AS publisher, b.price
FROM book b
JOIN publisher p ON p.pub_id = b.pub_id
WHERE b.price = (SELECT MAX(price) FROM book)
   OR b.price = (SELECT MIN(price) FROM book);
Solutions to queries 21–30

21. Total number of books published by the publishers.

The wording is ambiguous. If it’s a single number, SELECT COUNT(*) FROM book;. I read it as “books per publisher plus a grand total”:

SELECT COALESCE(p.name, 'TOTAL') AS publisher, COUNT(*) AS book_count
FROM book b
JOIN publisher p ON p.pub_id = b.pub_id
GROUP BY p.name WITH ROLLUP;

22. Books and their publisher that have at least one loan returned on time.

SELECT b.title, p.name AS publisher
FROM book b
JOIN publisher p ON p.pub_id = b.pub_id
WHERE EXISTS (
  SELECT 1 FROM loan l
  WHERE l.book_id = b.book_id
    AND l.returned_at IS NOT NULL
    AND l.returned_at <= l.due_date
);

“Not late” needs a precise definition: I take it as returned and returned by the due date. An open loan that isn’t due yet can’t be judged.

23. Members who have borrowed books, with their loan count, highest first.

SELECT m.card_no, m.surname, m.given_name, COUNT(*) AS loan_count
FROM member m
JOIN loan l ON l.card_no = m.card_no
GROUP BY m.card_no, m.surname, m.given_name
ORDER BY loan_count DESC;

Unlike #19, this uses an INNER JOIN because we only want members who have borrowed.

24. Books per publisher, only publishers with at least two books.

SELECT p.pub_id, p.name, COUNT(*) AS book_count
FROM publisher p
JOIN book b ON b.pub_id = p.pub_id
GROUP BY p.pub_id, p.name
HAVING COUNT(*) >= 2;

25. Members who borrowed crime story books at least twice.

SELECT m.card_no, m.surname, m.given_name, COUNT(*) AS crime_loans
FROM member m
JOIN loan l ON l.card_no = m.card_no
JOIN book b ON b.book_id = l.book_id
WHERE b.type = 'crime story'
GROUP BY m.card_no, m.surname, m.given_name
HAVING COUNT(*) >= 2;

The genre filter goes in WHERE (before grouping); the count threshold goes in HAVING (after). If it should be “two different books”, use COUNT(DISTINCT l.book_id).

26. Books currently out, their date out, and how many days overdue.

SELECT b.title, l.date_out,
       GREATEST(DATEDIFF(CURRENT_DATE, l.due_date), 0) AS days_overdue
FROM loan l
JOIN book b ON b.book_id = l.book_id
WHERE l.returned_at IS NULL;

MySQL’s DATEDIFF(a, b) returns a - b in days. GREATEST(..., 0) shows 0 instead of a negative number for books that aren’t due yet. PostgreSQL: GREATEST(CURRENT_DATE - l.due_date::date, 0).

27. Title and publisher of books still out and more than 7 days overdue.

SELECT b.title, p.name AS publisher,
       DATEDIFF(CURRENT_DATE, l.due_date) AS days_overdue
FROM loan l
JOIN book b      ON b.book_id = l.book_id
JOIN publisher p ON p.pub_id = b.pub_id
WHERE l.returned_at IS NULL
  AND l.due_date < CURRENT_DATE - INTERVAL 7 DAY;

The condition compares the bare due_date to a constant so an index can be used (see the “Wrapping a column in a function” block above).

28. How many slips each staff member issued today.

SELECT e.emp_id, e.surname, e.given_name, COUNT(l.invoice_no) AS invoices_today
FROM employee e
LEFT JOIN loan l
       ON l.emp_id = e.emp_id
      AND l.date_out >= CURRENT_DATE
      AND l.date_out <  CURRENT_DATE + INTERVAL 1 DAY
GROUP BY e.emp_id, e.surname, e.given_name;

29. Books with at least one loan whose publisher is in Hanoi, with their loan count.

SELECT b.title, COUNT(*) AS loan_count
FROM book b
JOIN publisher p ON p.pub_id = b.pub_id
JOIN loan l      ON l.book_id = b.book_id
WHERE p.city = 'Hanoi'
GROUP BY b.book_id, b.title;

30. Books cheaper than the overall average price and published in Hanoi.

SELECT b.title, b.price
FROM book b
JOIN publisher p ON p.pub_id = b.pub_id
WHERE p.city = 'Hanoi'
  AND b.price < (SELECT AVG(price) FROM book);

“The average price of all books” is the average of the whole table, so the subquery doesn’t filter by city. If the brief meant the Hanoi-only average, the subquery would need the same join and city filter. Another point to clarify with the requirement’s author.

7. What a senior would add before production

Key idea: the solution is correct for the brief, but a real system has to survive concurrent users and years of data. Four points I’d raise in review: an atomic return, indexes for hot queries, the choice of primary key, and old data.

7.1. Returning a book and charging the penalty in one statement

The naive version: load the loan into Java, compute the days overdue, set returnedAt and penalty, then save. Two librarians clicking “return” at once (or one person double-clicking) run two read-modify-write cycles (read, change in the app, write back). Fold it into one conditional UPDATE:

UPDATE loan
SET returned_at = NOW(),
    penalty     = GREATEST(DATEDIFF(NOW(), due_date), 0) * 0.500   -- 0.5 per day late
WHERE invoice_no = 1003
  AND returned_at IS NULL;

7.2. Indexes for the hot queries

The queries a real library runs most: “overdue loans” (#7, #27), “books member X currently has” and “today’s slips” (#28). The idx_loan_emp_date (emp_id, date_out) index already serves #28. For the overdue query I’d add:

CREATE INDEX idx_loan_open_due ON loan (returned_at, due_date);
  • MySQL treats returned_at IS NULL as an equality on the first column, so due_date < ? can still range-scan the second.
  • PostgreSQL is neater: a partial index ON loan (due_date) WHERE returned_at IS NULL holds only open loans and is far smaller.

How to check this with EXPLAIN is covered in indexes and query performance.

7.3. Natural key or surrogate key

7.4. Data that grows over time

The loan table only ever grows. After a few years, closed loans make up almost all of it but are rarely read. Two approaches:

  • A batch job that moves loans closed more than N years ago into loan_archive.
  • Partitioning by date_out so an old partition can be dropped in one statement. But MySQL doesn’t support foreign keys on partitioned tables, so this decision has a cost and belongs at design time.

Remember

  • Explain why UNIQUE(book_id) is wrong and how to emulate a partial unique index on MySQL.
  • Know why database constraints are still needed after Java validation (races, many write paths).
  • Write a “each X with its count” query that still shows zeros, and explain WHERE vs HAVING.
  • Know how to write sargable date conditions with a half-open range, and mind the time zone.
  • Know how to make “return a book” idempotent with UPDATE ... WHERE returned_at IS NULL.

Interview questions

Junior

Why does query #19 use LEFT JOIN and COUNT(l.invoice_no) rather than COUNT(*)?

So members who’ve never borrowed still appear, and show 0 rather than 1.

LEFT JOIN keeps those members, with the loan columns set to NULL. COUNT(*) counts rows, so they’d still count as 1. COUNT(l.invoice_no) counts only non-NULL values, so it correctly gives 0.

In query #25 the genre condition is in WHERE but the count threshold is in HAVING. Why?

WHERE filters rows before grouping; HAVING filters groups after.

Filtering the genre in WHERE is both correct and reduces the rows to group. HAVING is the only place you can use an aggregate like COUNT(*) >= 2. Putting the genre condition in HAVING is either a syntax error (the column isn’t grouped) or slower.

Mid

How do you guarantee “at most one open loan per book” in the database?

A unique index that only covers open loans: a partial unique index in PostgreSQL, a generated column + unique key in MySQL.

  • PostgreSQL: UNIQUE (book_id) WHERE returned_at IS NULL.
  • MySQL: a generated column equal to book_id while the loan is open and NULL afterwards, with a unique key on it (unique allows many NULLs).

An application-only check isn’t enough because of races between concurrent requests.

We already validate in Java. Do we still need NOT NULL, CHECK and FKs in the database?

Yes. The database is the last gate that every write path goes through.

Migration scripts, batch jobs, another service, someone running SQL by hand: none of them go through your Java code. Check-then-act also isn’t safe under concurrency. Database constraints are cheap, always enforced, and living documentation of the business rules. Application validation is still needed for friendly errors; the two layers complement each other.

Senior

The loan table reaches 50 million rows and the “overdue loans” screen keeps getting slower. What do you do?

Measure with EXPLAIN, add an index aimed only at open loans, and longer term archive old loans so the hot table shrinks.

  • Check for a full scan. The query filters on returned_at IS NULL AND due_date < ?, so add an index on (returned_at, due_date) in MySQL or a partial index on (due_date) WHERE returned_at IS NULL in PostgreSQL; open loans are a small fraction, so the index stays compact.
  • Long list: keyset pagination instead of OFFSET.
  • Longer term: archive long-closed loans into a separate table.
  • Aggregate reports still heavy: move them to a read replica or a warehouse, not the primary.
The brief is ambiguous (“delete books”, “not late”). What do you do?

Don’t guess and code: list the possible readings, propose a default with a reason, and confirm it with whoever owns the requirement.

Spell out the consequence of each reading: a hard delete loses loan history and hits the FK; does “not late” include open loans that aren’t due yet? Turn the agreed answer into a test case or put it in the spec so nobody has to ask again. For data, the safe default is not losing information: soft delete rather than hard delete.

Useful phrases for interviews

  • “The requirement is ambiguous here, so I’d confirm with the product owner whether ‘delete’ means a hard or a soft delete.”
  • “I’d enforce that invariant with a partial unique index rather than relying on a check in the service layer.”
  • “Before writing any SQL, I check whether the model can actually answer the business questions; here it couldn’t tell returned books from open loans.”
  • “MySQL has no partial indexes, so I emulate one with a generated column and a unique key, since unique indexes allow multiple NULLs.”
  • “I’d use EXISTS here because it’s a semi-join, so it won’t duplicate rows the way a plain join would.”
  • “Wrapping the column in a function makes the predicate non-sargable, so I compare the bare column against a precomputed boundary.”
  • “I compute ‘today’ in the application using the business time zone and pass the boundaries in as parameters.”
  • “The return is a conditional update guarded by returned_at IS NULL, so a double click is harmless: the second call updates zero rows.”
  • “I’d prefer a surrogate primary key and keep the card number as a unique business key, because card numbers can change.”

Notes based on the Fundamentals of Backend course (Ronin Engineer), rewritten in my own words.