Database Design
16 schema design prompts: canonical tables, indexes, and the key decisions to defend. Pair this with Database Concepts for background on SQL vs NoSQL, ACID, and sharding.
Design the schema for a chat product that supports 1:1 and group conversations, persistent message history, per-user read receipts, and presence. Optimize for the dominant read pattern: 'load the last N messages in a conversation, newest first.'
- Store users, conversations (1:1 and group), and messages with ordering guarantees.
- Look up the last N messages in a conversation in O(log n) keyed on conversation_id.
- Track per-user last-read position so unread counts are cheap.
Design the schema for a catalog that supports products with variants (size, color), nested categories, multi-currency pricing, and per-warehouse inventory. Read-heavy: product detail pages and category listings dominate traffic.
- Model products with arbitrary variant axes without schema changes per product type.
- Render a product detail page in one round trip (product, variants, prices, stock).
- Browse by category with filters on price, availability, and attributes.
Design the schema for a B2B SaaS where every row belongs to a tenant (workspace). Pick an isolation strategy and justify it. The product has users, projects, and tasks; tenants range from 5 to 50,000 seats.
- Enforce that no query accidentally crosses tenant boundaries.
- Support per-tenant data export and full deletion within a bounded window.
- Allow most queries to be served from a single tenant's working set in memory.
Design the schema for a booking system (hotel rooms, restaurant tables, or meeting rooms, pick one). Holds expire after a TTL if the user does not check out. Prevent any two confirmed bookings from overlapping for the same resource.
- Reserve a resource for a time range with no overlapping confirmed bookings.
- Hold a slot during checkout with automatic expiry if payment doesn't complete.
- Look up availability for a resource over a date range in O(log n).
Design the schema for a URL shortener (bit.ly clone) supporting custom short codes, per-link click analytics, optional expiry, and custom branded domains. Traffic is heavily skewed: 99% reads, 1% writes, with a long tail on click events.
- Resolve a short code to its long URL in under 10ms p99.
- Generate non-guessable short codes that don't collide.
- Record every click with enough context to power per-link dashboards.
Design the schema for an append-only audit log capturing actor, action, target, and metadata. Retention is 7 years for compliance. The product must answer 'what did user X do' and 'what happened to object Y' both quickly. Writes vastly outnumber reads.
- Sustain very high insert throughput with bounded latency.
- Answer 'show all actions by user X in date range D' efficiently.
- Answer 'show all events targeting object Y' efficiently.
Design the schema for a SaaS billing system: customers subscribe to plans, get billed on a cycle, and may upgrade / downgrade with proration mid-cycle. Failed payments retry on a schedule. Every monetary movement must be auditable.
- Subscribe a customer to a plan with a billing cycle anchor.
- Generate invoices at cycle boundaries and on plan changes.
- Apply proration when a customer changes plans mid-cycle.
Design the schema for an Uber-like service: riders request trips, drivers accept them, and the system must find the nearest available drivers to a pickup point in real time. Trip history powers earnings and dispute resolution.
- Find the K nearest available drivers to a lat/lng in under 100ms.
- Track driver state (offline / available / on_trip) with safe transitions.
- Persist trip lifecycle: requested, matched, started, ended, settled.
Design the schema for a Zendesk-like support system: customers file tickets, tickets flow through queues, agents work them, and SLAs are tracked per priority. Reports show breached SLAs and agent throughput.
- File a ticket with priority, severity, and queue assignment.
- Track every message in a thread (customer + agent + system).
- Compute first-response and resolution SLA per ticket, with breach status.
Design the schema for the double-entry ledger behind a wallet or payments product. Every money movement posts balanced debit and credit entries across accounts, balances must be correct at any point in time, and posted history is never edited.
- Model accounts with a type (asset, liability, equity, revenue, or expense) and a currency.
- Post a transaction as two or more entries whose debits equal its credits.
- Reject duplicate postings from retried requests.
Design the schema for a food delivery marketplace: restaurants publish menus with item options (size, add-ons), customers place orders, couriers pick them up, and each order moves through a status lifecycle that customers watch live.
- Store restaurants with opening hours and a manual pause switch.
- Model menus with categories, items, option groups, and options with price changes.
- Place an order that snapshots item names, options, and prices.
Design the metadata schema for a cloud drive: users organize files in nested folders, files keep version history, and any file or folder can be shared with users or groups as viewer or editor, with folder shares applying to everything inside.
- Store folders and files in a tree where each item has one parent.
- List a folder's children sorted by name or modified time.
- Keep every version of a file with its content hash and size.
Design the storage schema for an IoT platform ingesting readings from 2 million devices, each reporting several metrics every 10 seconds. Dashboards show the last hour at full resolution and the last year as hourly rollups, and alerts fire when a reading crosses a threshold.
- Register devices with type, site, and firmware version.
- Ingest about 200,000 device reports per second, each with a device timestamp.
- Query one device's metrics over the last hour at full resolution.
Design the schema for an online course platform: instructors publish courses made of sections and lessons, students enroll, progress is tracked per lesson, quizzes are graded, and a certificate is issued on completion.
- Store courses with ordered sections and ordered lessons of different kinds (video, article, and quiz).
- Enroll students and record how they got access.
- Track per-lesson completion and the video resume position.
Design the schema for a music streaming library: artists, albums, and tracks; user playlists in a user-defined order; liked songs; follows; and play history that feeds Recently Played and monthly listener counts.
- Catalog artists, albums, and tracks with many-to-many artist credits.
- Create playlists and add, remove, and reorder tracks, allowing duplicates.
- Like tracks and follow artists, playlists, and users.