STEP 1 · THE FIRST MINUTE
The problem
Design the database for a recipe site. People post recipes, list the ingredients, and rate each other's recipes.
It is vague on purpose. The candidate does not open the editor. They take the first five minutes to decide what they are building.
STEP 2 · ABOUT FIVE MINUTES
Propose the requirements
The top three things the system must do, the non-functional ones with numbers, and the questions whose answers would change a table:
Functional (top 3): 1. Post a recipe with its ingredients and quantities 2. Rate a recipe from 1 to 5, once per person 3. Show a recipe page: ingredients and the average rating Non-functional: - Scale: about 50,000 recipes, read far more than written - Speed: a recipe page in under 100 ms - Must never be lost: recipes and their ingredients Questions: - Is "flour" one ingredient shared by every recipe, or text per recipe? - Can someone change their rating? - What happens to ratings when a recipe is deleted?
ASK YOURSELFWhy ask whether an ingredient is shared, instead of just starting?
Because the answer changes the shape. If flour is shared, it is a table of its own and a third table holds the quantity per recipe. If it is text per recipe, a column would do. A question whose answer changes a table is the kind worth spending interview time on; a question about which database to use is not.
ASK YOURSELFWhy put numbers on the non-functional requirements?
"Read far more than written" and "under 100 ms" tell you the recipe page is the hot path. That is exactly what the follow-up will press on, and a candidate who wrote it down saw it coming.
STEP 3 · THE INTERVIEWER ANSWERS
The scope is settled
Good. Ingredients are shared: flour is flour. You can change your rating, but never have two. A deleted recipe takes its ingredient list and its ratings with it. Deleting an ingredient that recipes still use should be refused.
Every sentence there is a constraint the database will enforce. Write them down; they are your tests.
STEP 4 · ABOUT TWO MINUTES
The entities
- users (already exists)
- recipes
- ingredients
- recipe_ingredients: which ingredient, in which recipe, how much
- ratings: who rated which recipe, how many stars
ASK YOURSELFWhy is recipe_ingredients an entity of its own?
"200 g" belongs neither to the recipe (it has many ingredients) nor to flour (it is in many recipes). It belongs to the pair. When an attribute belongs to a pair, the pair is a table, and the pair is its key.
STEP 5 · TEN TO FIFTEEN MINUTES
The schema
CREATE TABLE recipes (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
author_id BIGINT NOT NULL REFERENCES users (id),
title TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Shared: "flour" is one row, used by every recipe that needs flour.
CREATE TABLE ingredients (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name TEXT NOT NULL UNIQUE
);
-- The quantity belongs to the pair, so the pair is a table.
CREATE TABLE recipe_ingredients (
recipe_id BIGINT NOT NULL REFERENCES recipes (id) ON DELETE CASCADE,
ingredient_id BIGINT NOT NULL REFERENCES ingredients (id),
quantity NUMERIC(8, 2) NOT NULL CHECK (quantity > 0),
unit TEXT NOT NULL,
PRIMARY KEY (recipe_id, ingredient_id)
);
-- One rating per person per recipe: the key says so, and changing your
-- mind is an UPDATE of that one row.
CREATE TABLE ratings (
recipe_id BIGINT NOT NULL REFERENCES recipes (id) ON DELETE CASCADE,
user_id BIGINT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
stars SMALLINT NOT NULL CHECK (stars BETWEEN 1 AND 5),
PRIMARY KEY (recipe_id, user_id)
);Run on Postgres, this refuses what the scope refuses:
- a second rating by the same person (the key), while changing your rating is an update;
- six stars, or zero (the CHECK);
- the same ingredient twice in one recipe, and a quantity of zero;
- deleting an ingredient that a recipe still uses;
- and deleting a recipe takes its ingredient lines and its ratings with it.
ASK YOURSELFWhy CASCADE from recipes to ratings, but the default from ingredients?
A rating means nothing without its recipe, so they go together. An ingredient in use is still needed by other people's recipes: deleting it should be refused, not quietly remove lines from recipes its author never touched. The delete rule is a business decision written in the schema.
STEP 6 · THE LAST TEN MINUTES
A follow-up
The recipe page shows the average rating, and our best recipes have millions of ratings. The page is slow. What do you do?
The primary key starts with recipe_id, so finding a recipe's ratings is already indexed. The cost is reading all of them on every view. I would keep a count and a total on the recipe, updated in the same transaction as each rating, and the page reads one row.
ALTER TABLE recipes
ADD COLUMN rating_count INTEGER NOT NULL DEFAULT 0,
ADD COLUMN rating_total INTEGER NOT NULL DEFAULT 0;
-- Kept by the database in the same transaction as the rating, so no
-- application path can forget it.
CREATE FUNCTION keep_rating_totals() RETURNS trigger AS $$
BEGIN
IF TG_OP IN ('INSERT', 'UPDATE') THEN
UPDATE recipes SET rating_count = rating_count + 1,
rating_total = rating_total + NEW.stars
WHERE id = NEW.recipe_id;
END IF;
IF TG_OP IN ('DELETE', 'UPDATE') THEN
UPDATE recipes SET rating_count = rating_count - 1,
rating_total = rating_total - OLD.stars
WHERE id = OLD.recipe_id;
END IF;
RETURN NULL;
END $$ LANGUAGE plpgsql;
CREATE TRIGGER ratings_keep_totals
AFTER INSERT OR UPDATE OR DELETE ON ratings
FOR EACH ROW EXECUTE FUNCTION keep_rating_totals();
-- The page reads one row: rating_total / rating_count.Then the trade-off, said out loud: every rating now also writes the recipe's row, so a viral recipe becomes a hot row. At that scale the next step is batching the updates, and the candidate says so instead of pretending the design has no cost.
ASK YOURSELFWhy a count and a total, and not the average itself?
An average cannot be corrected when one rating changes unless you know how many ratings it averaged. A count and a total can be adjusted by one rating at a time, and the average is a division when the page reads it.
STEP 7 · THE CLOSE
The explanation
Ingredients are shared, so the quantity lives on recipe_ingredients, keyed by the pair. Ratings are keyed by recipe and person, which makes "once per person" a rule the database enforces. Deleting a recipe cascades to what only it owns; deleting an ingredient in use is refused. For the slow page I store a count and a total kept by a trigger, at the cost of a hot row on popular recipes. At ten times the traffic I would batch those updates.
Short, in the order of the decisions, each with its reason and its cost. That is what the interviewer writes down.
Your turn
The tutorial walks you through the same steps on a small problem, with a coach beside you. Then a real case, cold, against the clock.