Database Design
10 schema design prompts — canonical tables, indexes, and the key decisions to defend. Click any title to expand. Pair this with Database Concepts for the SQL-vs-NoSQL / ACID / sharding background.
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 (Time-To-Live) 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 (Uniform Resource Locator) 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 (Service Level Agreement) per ticket, with breach status.