When a company asks you to "design a database for X," they are not looking for a perfect answer. They want to see how you think: do you ask the right questions first, can you reason about trade-offs, do you understand why one design beats another? This guide walks through the interview playbook and seven classic design examples — so you arrive with a framework, not just facts.
Before drawing a single table, ask questions. How many requests per second? Is this mostly reads or writes? Can users sometimes see stale data, or does it always need to be current? How fast must queries respond? Will the schema change often? Every design decision — which indexes to add, whether to use a cache, when to shard — flows directly from these answers. Designing before clarifying is how you waste your interview time building the wrong thing.
Start with a clean, normalized design — one fact in one place, no duplication, foreign keys connecting tables properly. This shows you understand the fundamentals. Then, once you have that baseline, talk about where you might deliberately break the rules: "for the feed, I would denormalize the author name into the post row to avoid a JOIN on every read." Always start clean and justify each deviation. Never start with a messy schema and try to explain it after.
Do not jump straight to "we need to shard it." Walk through the progression: first add indexes so queries are fast. Then add read replicas so reads can scale horizontally. Then consider a cache like Redis to absorb the most frequent lookups. Only after those approaches reach their limits do you reach for sharding — and when you do, be specific about which column to shard on and why. Interviewers want to see that you scale incrementally, not that you over-engineer from the start.
Every design choice has a cost, and interviewers are listening for you to name it. If you add a cache: "reads get faster, but now writes need to invalidate the cache — there is a short window where the cache is stale." If you denormalize: "reads are faster, but now an update to the author's name requires updating every copy." If you use eventual consistency: "the system stays available during a network failure, but users might briefly see old data." Stating trade-offs shows maturity. Pretending there are none sounds naive.
The core table is: links(id BIGINT PK, short_code VARCHAR(8) UNIQUE, original_url TEXT, user_id FK, created_at, expires_at, click_count). The key insight: every person who clicks a short link is a read, but only the person who created the link was a write. The read-to-write ratio is roughly 300:1. The bottleneck is read throughput. The fix is a Redis cache keyed on short_code — 99% of redirects never touch the database at all. For click counting, avoid updating the same row thousands of times per second; use Redis INCR and flush to the database in batches. Short code generation: either base62 encode an auto-increment ID, or generate a random hash and check for collisions on insert.
You need a Posts table, a Users table, and a Follows table. The tricky question is: how does your feed get assembled? Pull model: when you open the app, query all the posts from everyone you follow and sort them. Simple, but if you follow 5000 people this query is expensive. Push model: when someone posts, immediately write a copy to every follower's feed table. Reading the feed is instant — just look up your own feed rows. But if a celebrity with 10 million followers posts, that is 10 million writes. The hybrid approach: push for normal users, pull for celebrities. Shard the feed table by user_id so each user's feed lives on one machine.
You need products(id, name, price) and inventory(product_id, warehouse_id, quantity). The hard problem is overselling: two people buy the last item simultaneously and both get a confirmation. You prevent this with row-level locking (SELECT ... FOR UPDATE) so only one transaction can decrement at a time, plus a CHECK (quantity >= 0) constraint to block any update that would go negative. For items sitting in a shopping cart, use a separate reservations table with an expiry — the item is held for 10 minutes; if payment does not complete, it goes back into available inventory.
The core tables are: conversations(id, created_at) and messages(id, conv_id FK, sender_id FK, content, sent_at). The key insight for sharding is that when you load a conversation, you want all its messages on the same machine — so shard on conversation_id. Ordering matters: messages must appear in the order they were sent. Do not rely on sent_at alone — two messages sent within the same millisecond on different servers could have identical timestamps. Use a composite sort key of sent_at plus a UUID. Partition the messages table by month so old messages live in separate, smaller partitions that rarely need to be touched.
You need drivers(id, status, location) and rides(id, driver_id, rider_id, status, pickup, dropoff, price). The interesting problem is "find drivers near me." You cannot use a standard index for this — you need a geospatial index (PostGIS in PostgreSQL, or a hexagonal grid system like H3) that knows how to answer "within 2km of this coordinate." Do not store real-time driver locations in the relational database — a driver pings their location every few seconds, and at scale that is millions of writes per minute. Store live locations in Redis (which has a built-in geospatial data structure), and only write to the database when a ride is booked.
The tables are: rooms(id, hotel_id, type, base_price) and bookings(id, room_id, user_id, check_in, check_out, status). The hardest query is "which rooms are available for these dates?" — you need to find rooms that have no existing booking that overlaps your requested window. Index on (room_id, check_in, check_out) to make this fast. To prevent two people from booking the same room at the same time, lock the room's booking rows with SELECT ... FOR UPDATE before inserting. PostgreSQL even supports exclusion constraints on date ranges that will reject overlapping bookings at the database level.
The key design decision: never update or delete financial records. Use an append-only (immutable) ledger: transactions(id, from_account_id, to_account_id, amount, created_at, idempotency_key UNIQUE). Every row is a permanent record of something that happened. A balance is the sum of all credits minus all debits for an account — or a pre-computed total in a separate balance table updated atomically alongside each insert. The idempotency_key is critical: if the network drops during a payment and the client retries, the UNIQUE constraint on idempotency_key ensures the charge only goes through once. A separate payment_intents table tracks where a payment is in its multi-step lifecycle (created, authorized, captured, refunded).
Pattern 1: Optimistic locking with version column (prevents overselling) UPDATE inventory SET quantity = quantity - 1, version = version + 1 WHERE product_id = 42 AND version = :expected_version AND quantity > 0; -- Check rows affected; if 0: concurrent update happened, retry Pattern 2: Soft delete with audit trail + partial index ALTER TABLE orders ADD COLUMN deleted_at TIMESTAMPTZ; CREATE INDEX idx_orders_active ON orders (user_id) WHERE deleted_at IS NULL; only active orders in the index Pattern 3: Idempotency key for safe retries INSERT INTO transactions (id, from_account, to_account, amount, idempotency_key) VALUES (gen_random_uuid(), 1, 2, 100.00, :client_key) ON CONFLICT (idempotency_key) DO NOTHING; -- Safe to retry on network timeout — duplicate becomes no-op Pattern 4: Booking availability check (prevent double-book) SELECT 1 FROM bookings WHERE room_id = :room_id AND status != 'cancelled' AND check_in < :requested_checkout AND check_out > :requested_checkin FOR UPDATE; lock conflicting rows, prevents race condition Pattern 5: Polymorphic comments table CREATE TABLE comments ( id BIGSERIAL PRIMARY KEY, commentable_type VARCHAR(50), 'Post', 'Product', 'Video' commentable_id BIGINT, body TEXT, created_at TIMESTAMPTZ DEFAULT now() ); CREATE INDEX ON comments (commentable_type, commentable_id);
Drag tables to arrange them. Click "Add Table" to create a new one. Click "Export SQL" to generate CREATE TABLE statements — useful for practicing design interviews.
Use this flow for every system design question. The interviewer wants to see structured thinking and named trade-offs — not a perfect design. Spend 50% of your time on the deep-dive and trade-offs sections; that is where differentiation lives.
Schema design is driven by how data is read and written. The same data can have radically different optimal schemas for read-heavy vs write-heavy workloads. Always ask before designing — an interviewer who gives you a vague problem is testing whether you clarify.
"God table" with 100+ columns, most NULL for any given row type. Discriminator columns, sparse columns, and huge row sizes kill performance and readability. Use proper inheritance or polymorphic patterns instead.
You will always eventually need to know when a row was created or modified for debugging, auditing, and incremental sync. Add these to every table from day one — retrofitting them onto a live 100M-row table is painful.
Picking 255 "because it fits" prevents the database from making good storage and statistics decisions. Use the actual expected max length. For names: VARCHAR(100). For emails: VARCHAR(254). For URLs: TEXT. For phone numbers: VARCHAR(20).
Designing a schema that requires loading a list then fetching related data per item creates N+1 queries. Design for co-location: data that is always read together should live together or have indexes that make single-query fetches efficient.
links(id BIGINT PK, short_code CHAR(8) UNIQUE, long_url TEXT, user_id INT, created_at, expires_at, is_active BOOL); clicks(id BIGINT, link_id INT, ip TEXT, user_agent TEXT, clicked_at) PARTITIONED BY MONTH. Index: unique index on short_code; partial index WHERE is_active=true. Scale: Redis cache for short_code→long_url (LRU, 24h TTL handles 99% of traffic). Read replicas for remaining DB reads. Shard links by hash(short_code). Click analytics: batch-write to Kafka → aggregate to ClickHouse. Short code generation: base62(auto_increment) or hash, collision-check on insert.includes in ActiveRecord, prefetch_related in Django). (3) Batch: fetch all parent IDs, then WHERE parent_id IN (ids) — 2 queries instead of N+1. (4) DataLoader pattern (GraphQL) — batches per request frame.likes(user_id, post_id, created_at, PRIMARY KEY(user_id, post_id)). Works to ~100M rows. Problems at scale: (1) COUNT(*) per post is slow; (2) viral post = hot partition. Scalable approach: Store post_like_count as a denormalized counter on posts, increment/decrement atomically. For exact user-post checks, use Redis Sets: SISMEMBER post:{id}:likes {user_id} — O(1). Persist asynchronously to DB for durability. For ultra-scale: HyperLogLog for approximate counts, exact counts in Redis, DB as persistent backup. Shard likes table by post_id for fast per-post queries but cap hot posts with caching.