Design Problem Bank: From Library to Multi-Tenant SaaS

In one line

Graded schema design problems with the key insight each one tests. Beginner problems test entities, keys and constraints; intermediate ones test concurrency, inventory and time ranges; advanced ones test ledgers, feeds, sharding, multi-tenancy and polyglot pipelines.

0/5 · 0%

Think of it like this

Practice matches before a tournament. Each one is chosen to train a specific skill.

Key ideas

01
Beginner: library (catalogue vs copy, open-loan uniqueness), student management (many-to-many enrolments with grades), employee management (self-referencing manager hierarchy, recursive CTE), blog (posts, tags, comments, slugs unique per author), expense tracker (money types, categories, monthly aggregation).
02
Intermediate: e-commerce (immutable order lines), hotel booking (per-night inventory or exclusion constraints), movie booking (atomic seat holds), food delivery (order state machine, rider locations outside OLTP), inventory (reservations and movement ledger), learning platform (ordered sections, progress), job portal (search via Elasticsearch, applications unique per job), hospital (appointments without double-booking a doctor).
03
Advanced: banking and payments (double-entry ledger, idempotency, reconciliation), Uber (geo index + trips), Instagram (hybrid feed), WhatsApp (per-conversation order, read markers), YouTube (video metadata vs blob storage, view counts via streams), Netflix (catalogue, viewing history in wide-column store, per-profile resume points), Amazon (sharded orders, seller read model), multi-tenant SaaS (tenant_id + RLS, tiered isolation), notification platform (dedup, scheduling), search platform (CDC to Elasticsearch, reindex with aliases), analytics platform (stream → warehouse, star schema).
04
For each problem, name the one invariant that must never break (no double booking, balanced ledger, one active loan per copy) and show exactly how the schema enforces it.

Code & diagrams

problem-insights.txttext

Library          -> partial unique index: one open loan per copy
Hospital         -> exclusion constraint: doctor slots never overlap
Movie booking    -> atomic conditional UPDATE on seat rows + hold expiry
Hotel            -> per-night inventory rows, all-or-nothing update
E-commerce       -> order lines copy price; order state machine
Banking          -> double-entry, append-only, sum(entries) = 0 per txn
Instagram        -> hybrid fan-out; follower graph stored both directions
WhatsApp         -> (conversation, bucket) partitions; per-user read marker
YouTube          -> metadata in SQL, video in object storage, views via stream
Multi-tenant     -> tenant_id in every PK/FK + RLS; big tenants isolated
Search platform  -> DB is truth; CDC -> index; alias swap reindex

Interview problem

The problem

Timed mock: hospital appointment system

Design the database for a hospital: patients, doctors with weekly schedules and exceptions (leave), appointment booking in 15-minute slots across 40 departments, no double booking, cancellations, and prescriptions. 5,000 bookings/day.

When it breaks

Designing without naming the invariant

What you see

The schema looks reasonable but double bookings are possible under concurrency; the interviewer asks "what if two receptionists book at once?" and the design has no answer.

Fix & prevent

State the invariant first and enforce it in the database (constraint or atomic conditional write), then explain behaviour under concurrency.

Explain it without notes

What is the key invariant in a double-entry ledger and how is it enforced?

Practice

Do three problems (one per level) in 35 minutes each using the template; record what you missed.

Trade-offs

↔
Breadth across domains builds pattern recognition; depth on a few builds the ability to defend decisions. Do both.

Done when you can

I can solve beginner through advanced schema design problems and name each one's invariant.

Command Palette