Schema Design for LLD

Intermediate · 22 min read · Low-Level Design, Concurrency & Code Craft

Mapping classes to tables, invariants as constraints, indexes from queries, and concurrency in SQL.

Many LLD rounds end with "now show me the tables" — and some (Amazon, Flipkart, fintechs) ask for the schema up front. The interviewer wants three things: a faithful mapping of your class model to tables, constraints that enforce the invariants your code relies on (so a bug or a race can't corrupt data), and indexes…

1. Mapping classes to tables

Two habits that interviewers notice:

2. Invariants belong in constraints

Your Java code checks "a seat can't be booked twice". But two servers, a retried request or a future bug can all bypass that check. The database is the last line of defence, and constraints are how you tell it the rules:

3. Movie booking: the unit of contention is (show, seat)

The same split as the class model: seat is the physical seat (never changes); show_seat is its availability for one show. When a show is created, insert one show_seat row per seat.

4. "One active X": partial unique indexes

A parking spot can have many tickets over time but only one open ticket; a plate can't be parked twice at once:

5. Ranges that must not overlap: exclusion constraints

"No two confirmed bookings for the same room have overlapping stays" is exactly one constraint. DATERANGE is half-open by default, so a checkout and a check-in on the same day don't conflict. Finding free rooms is the same operator:

6. Inheritance: three strategies

PaymentMethod with subtypes Card and Upi:

7. Money: ledger tables

A transfer is one database transaction: lock both account rows in id order (SELECT ... FROM account WHERE id IN (:a, :b) ORDER BY id FOR UPDATE), insert the transaction (the unique key rejects a duplicate), insert two ledger legs, update both balances (the CHECK rejects an overdraft and rolls everything back).

8. Indexes come from queries

List the queries first, then index for them:

9. Concurrency control in the database

Interview questions

  • How do you guarantee a seat can't be double-booked even if two app servers race?
  • How would you store a class hierarchy such as Card and UPI payment methods?
  • Your entity has 'only one active' semantics — one open ticket per spot, one default address per user. How do you enforce it?

Further reading

Key takeaways

  • Map entities to tables, value objects to embedded columns, enums to CHECKed text, compositions to cascading foreign keys.
  • Put every invariant you can into constraints: CHECK, UNIQUE, partial unique indexes, exclusion constraints, foreign keys.
  • Claim contended resources with conditional UPDATEs and check the row count; lock multiple rows in a fixed order.
  • Design indexes from the query list: equality columns first, then ranges.
  • Verify cross-row invariants (ledgers) with reconciliation queries.

Previous: Code Smells & Refactoring · Next: Machine-Coding Walkthrough: Snake & Ladder