Chapter 15: Schema Design Interview Exercises

Why this matters

Schema design exercises test the same judgment this entire book has built chapter by chapter — normalization vs. denormalization, relational vs. NoSQL fit, indexing for the actual access pattern — compressed into a single, time-boxed exercise. Working through two representative exercises in full, with the reasoning made explicit, builds the pattern-matching needed for any similar prompt.

Exercise 1: Design a schema for a URL shortener

Requirements: users submit a long URL and receive a short code; visiting the short URL redirects to the original; track click counts.

Reasoning, out loud, the way a strong candidate would present it: "This is fundamentally simple, structured data with one dominant access pattern — 'look up the long URL by its short code' — and click counting needs to handle very high write volume (every single click) without slowing down the redirect itself. I'd use a relational database for the core mapping (structured, needs uniqueness constraints) and consider a separate, faster mechanism for the click counter specifically, since counting shouldn't block or slow down the redirect response."

CREATE TABLE urls (
    id SERIAL PRIMARY KEY,
    short_code VARCHAR(10) NOT NULL UNIQUE,   -- UNIQUE constraint: structurally prevents collisions
    long_url TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT now(),
    expires_at TIMESTAMP  -- nullable: not every URL needs an expiration
);
CREATE INDEX idx_urls_short_code ON urls(short_code);  -- the dominant, exact-match lookup pattern

Click counting — a deliberate, justified use of a second data store (Chapter 1's polyglot persistence):

-- Redis, incrementing a counter on every click -- extremely fast, doesn't touch
-- the relational database's write path on the hot redirect request at all:
INCR clicks:abc123

-- Periodically (e.g., every minute, via a background job), flush accumulated
-- counts from Redis into the relational database for durable, queryable storage:
UPDATE urls SET click_count = click_count + <redis_delta> WHERE short_code = 'abc123';

Trade-offs a strong candidate would raise: the async click-count flush means the durably-stored count can lag the true real-time count slightly (an explicit, deliberate eventual-consistency choice, Chapter 9) — acceptable here because click counts are an analytics/display feature, not a value with strict correctness requirements (unlike, say, the inventory count from Chapter 3). Short code generation (a separate concern from this schema) needs to avoid collisions — worth mentioning even briefly to show awareness of a related problem outside the immediate schema question.

Exercise 2: Design a schema for an e-commerce inventory and order system

Requirements: products have stock levels; customers place orders for one or more products; stock must never go negative even under concurrent purchases; support order history queries per customer.

Reasoning, out loud: "This has real transactional requirements — stock must never go negative, which is exactly the race condition from Chapter 3 — so this calls for a relational database with proper transaction handling, not an eventually-consistent NoSQL store where that guarantee would be much harder to enforce."

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price_cents INTEGER NOT NULL CHECK (price_cents > 0),
    stock_quantity INTEGER NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0)  -- Chapter 2's structural invariant
);

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    created_at TIMESTAMP NOT NULL DEFAULT now(),
    status VARCHAR(20) NOT NULL DEFAULT 'pending'
);

CREATE TABLE order_items (
    order_id INTEGER NOT NULL REFERENCES orders(id),
    product_id INTEGER NOT NULL REFERENCES products(id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    price_cents_snapshot INTEGER NOT NULL,  -- Chapter 2's deliberate denormalization: the price AT PURCHASE TIME
    PRIMARY KEY (order_id, product_id)
);

CREATE INDEX idx_orders_customer_id ON orders(customer_id);  -- the "order history per customer" access pattern

The purchase transaction, applying Chapter 3 directly:

BEGIN;
SELECT stock_quantity FROM products WHERE id = 42 FOR UPDATE;  -- lock, preventing the race condition
-- application checks stock_quantity >= requested quantity
UPDATE products SET stock_quantity = stock_quantity - 2 WHERE id = 42;
INSERT INTO orders (customer_id) VALUES (7) RETURNING id;
INSERT INTO order_items (order_id, product_id, quantity, price_cents_snapshot) VALUES (...);
COMMIT;

Trade-offs a strong candidate would raise: price_cents_snapshot is deliberate denormalization (Chapter 2) — an order's receipt must show what the customer actually paid, not the product's current price. The SELECT ... FOR UPDATE lock (Chapter 3) is the specific mechanism preventing Chapter 1's original oversell bug; a candidate should explicitly connect this design choice back to the concurrency problem it solves, not just include it without justification. If this needed to scale far beyond one node, sharding orders by customer_id (Chapter 11) would align with the dominant "order history per customer" query — worth mentioning as a future scaling path, without over-engineering the initial design for a need that isn't yet real.

Common mistakes

1. Jumping straight to a schema without stating the dominant access pattern(s) first — both exercises above start with "what's actually being queried/protected," which drives every subsequent design decision.
2. Choosing a NoSQL store for data with genuine multi-record transactional requirements (like the inventory exercise) without acknowledging the added difficulty of enforcing that guarantee outside a relational transaction.
3. Forgetting to include the specific mechanism (constraints, locks, indexes) that makes the design actually work, rather than just describing tables — a schema without CHECK (stock_quantity >= 0) and FOR UPDATE doesn't actually solve the stated requirement of "stock must never go negative."
4. Not connecting design choices back to the specific requirement they satisfy — every table, constraint, and index in a strong answer should be traceable to a specific stated (or reasonably inferred) requirement, not included by default habit.

Discussion / interview questions

1. "Design a schema for a URL shortener, and explain your choice of database technology for each part." Strong answer: Relational for the core URL mapping (structured, needs a uniqueness constraint), with an optional separate fast counter (Redis) for click tracking specifically, periodically flushed to durable storage — justifying each choice by its specific access pattern and consistency requirement, not defaulting to one technology for everything.

2. "How would you ensure stock never goes negative even under concurrent purchase requests, in your schema design?" Strong answer: A CHECK (stock_quantity >= 0) constraint as a structural backstop, combined with SELECT ... FOR UPDATE within a transaction to serialize the check-and-decrement sequence — explicitly connecting this back to the race condition it prevents (Chapter 3), not just stating the mechanism without justification.

3. "Why would you snapshot a product's price into the order_items table instead of joining to the current products table?" Strong answer: An order's historical record should reflect what the customer actually paid at purchase time, which can differ from the product's current price if it's since changed — a deliberate, justified denormalization (Chapter 2), not an oversight.

4. "If this order system needed to scale far beyond a single database node, what would you consider?" Strong answer: Sharding orders by customer_id (Chapter 11), since that aligns with the dominant "order history per customer" query pattern — while being explicit that this is a future consideration, not something to over-engineer into the initial design without a real, current scaling need.

Chapter summary / cheat sheet

• Every schema design exercise should start with stating the dominant access pattern(s) and any hard correctness requirements (like "stock must never go negative") before drawing any tables.
• Choose relational vs. NoSQL (and any polyglot combination) based on those stated requirements, explicitly — not by default habit.
• Include the actual enforcement mechanism (constraints, locks, indexes) that makes a stated requirement true, not just a plausible-looking set of tables.
• Connect every design decision back to a specific requirement it satisfies — this is what separates a schema that merely looks reasonable from one that's been reasoned through.

---