Every data engineer eventually runs into the same apparent contradiction: the database design that every textbook, every senior review, and every "how do I avoid duplicate data" instinct insists is correct turns out to be the wrong shape the moment someone asks a real business question about it. That's not a contradiction. It's two different jobs sharing one word — "database" — when they actually want opposite things from how the data is laid out.
This article walks both halves, in order, on one running example: build a properly normalized schema from a genuinely messy starting point, watch it become painful the moment someone wants to ask something of it, and then deliberately undo the normalization — on purpose, for a documented reason — into a star schema built for exactly that question.
Meet Curb Appetite
Curb Appetite is a food delivery app: customers order from local restaurants, a driver picks it up and delivers it, everyone involved generates data. The starting point is the kind of table that actually exists in a lot of early-stage companies — a flat export somebody built to get the app shipped, never designed, just grown:
| order_id | order_date | customer_name | customer_email | customer_city | customer_state | customer_zip | restaurant_name | restaurant_cuisine | driver_name | driver_phone | items_ordered | order_total |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 5001 | 2026-03-14 | Priya Shah | priya@example.com | Austin | TX | 78701 | Bangkok Nights | Thai | Marcus Webb | 512-555-0142 | Pad Thai x2, Spring Rolls x1, Thai Iced Tea x1 | 38.50 |
| 5002 | 2026-03-14 | Diego Ruiz | diego@example.com | Austin | TX | 78701 | Bangkok Nights | Thai | Alicia Nguyen | 512-555-0198 | Green Curry x1, Thai Iced Tea x2 | 23.00 |
This table works, in the sense that it renders a receipt. It's also a small museum of everything normalization exists to fix, and every violation in it will cost someone real time later. Let's fix them in order.
Getting to First Normal Form: atomic values, no repeating groups
First Normal Form (1NF) requires that every column hold a single, atomic value — no lists, no repeating groups crammed into one field — and that every row be uniquely identifiable.
items_ordered fails immediately: "Pad Thai x2, Spring Rolls x1, Thai Iced Tea x1" is three facts wearing one column. Ask "how many Pad Thais did we sell this month" against this table and the honest answer is: you can't, not with SQL — you'd need to parse a string first. That's the tell for a 1NF violation: if answering a normal-sounding question requires string-splitting a column, the column is doing the job of a table.
The fix is to give each item its own row:
-- order_items, one row per item on an order
-- (not yet normalized further — watch what's still duplicated)
CREATE TABLE order_items (
order_id TEXT NOT NULL REFERENCES orders(order_id),
menu_item_id TEXT NOT NULL,
item_name TEXT NOT NULL,
item_price NUMERIC(6,2) NOT NULL,
quantity INT NOT NULL,
PRIMARY KEY (order_id, menu_item_id)
);
order_id menu_item_id item_name item_price quantity
5001 MI-101 Pad Thai 14.00 2
5001 MI-102 Spring Rolls 6.50 1
5001 MI-103 Thai Iced Tea 4.00 1
5002 MI-104 Green Curry 15.00 1
5002 MI-103 Thai Iced Tea 4.00 2
items_ordered is gone from orders, replaced by this table. Every value is now atomic, and "how many Pad Thais did we sell" is SUM(quantity) WHERE item_name = 'Pad Thai' instead of a parsing exercise. Technically 1NF-compliant — but look at MI-103 appearing twice, at the same price, on two unrelated orders. That's not a coincidence, and it's not fixed yet.
Getting to Second Normal Form: no partial dependencies
Second Normal Form (2NF) requires 1NF, plus: every non-key column must depend on the entire primary key — not just part of it. This only ever bites when a table has a composite key, which order_items does: (order_id, menu_item_id).
Ask what item_name and item_price actually depend on, and the honest answer is: only menu_item_id. Bangkok Nights' Thai Iced Tea costs $4.00 regardless of which order it's attached to — order_id contributes nothing to that fact. That's a partial dependency, and it's exactly why MI-103 shows up twice with the same price above: the price isn't stored once, it's stored once per order line that happens to include it. Raise the price to $4.50 tomorrow and you have to find and update every historical row that references it, or old and new orders quietly disagree about what a Thai Iced Tea costs.
The fix is to extract what the item actually is from what was ordered:
CREATE TABLE menu_items (
menu_item_id TEXT PRIMARY KEY,
restaurant_id TEXT NOT NULL REFERENCES restaurants(restaurant_id),
item_name TEXT NOT NULL,
item_price NUMERIC(6,2) NOT NULL
);
CREATE TABLE order_items (
order_id TEXT NOT NULL REFERENCES orders(order_id),
menu_item_id TEXT NOT NULL REFERENCES menu_items(menu_item_id),
quantity INT NOT NULL,
PRIMARY KEY (order_id, menu_item_id)
);
Now Thai Iced Tea's price exists in exactly one row, in menu_items, and order_items just references it. One update, everywhere correct.
Worth a clarifying note here, because it trips people up: orders itself was never at risk of a 2NF violation, because its primary key (order_id) is a single column. 2NF violations are specifically about partial dependency on a composite key — with a single-column key, every non-key column depends on 100% of the key by definition, so there's no "partial" to violate. 2NF only ever does work on tables like order_items, where more than one column makes up the key.
Getting to Third Normal Form: no transitive dependencies
Third Normal Form (3NF) requires 2NF, plus: no non-key column may depend on another non-key column instead of on the primary key directly. This is called a transitive dependency, and orders is full of them.
Look at customer_city and customer_state. They don't actually describe the order — they describe the customer, by way of the customer's zip code. order_id → customer_id → zip → city/state is a chain, and 3NF says a column has to depend on the key directly, not by riding along on another attribute's coattails. The same thing is true of restaurant_name/restaurant_cuisine (they describe the restaurant, not the order) and driver_name/driver_phone (they describe the driver).
Notice something in the sample data above: both orders share zip = 78701, and both store "Austin", "TX" redundantly. That's the anomaly made visible — if a zip code's city assignment ever needs correcting, you're hunting down every order row that happens to reference it, instead of fixing one row in one place.
The fix, applied consistently:
CREATE TABLE zip_codes (
zip TEXT PRIMARY KEY,
city TEXT NOT NULL,
state TEXT NOT NULL
);
CREATE TABLE customers (
customer_id TEXT PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL,
zip TEXT NOT NULL REFERENCES zip_codes(zip)
);
CREATE TABLE restaurants (
restaurant_id TEXT PRIMARY KEY,
name TEXT NOT NULL,
cuisine TEXT NOT NULL
);
CREATE TABLE drivers (
driver_id TEXT PRIMARY KEY,
name TEXT NOT NULL,
phone TEXT NOT NULL
);
CREATE TABLE orders (
order_id TEXT PRIMARY KEY,
order_date DATE NOT NULL,
customer_id TEXT NOT NULL REFERENCES customers(customer_id),
restaurant_id TEXT NOT NULL REFERENCES restaurants(restaurant_id),
driver_id TEXT NOT NULL REFERENCES drivers(driver_id),
order_total NUMERIC(8,2) NOT NULL
);
orders has shrunk from thirteen columns to six. Every fact in the schema now lives in exactly one place, and every non-key column depends on nothing but its own table's primary key. Seven tables, zero duplicated facts, zero update anomalies. This is a genuinely good schema — for the job it's designed for.
Here's the full result as an entity-relationship diagram — the thing normalization was building toward the whole time:
This is a good schema. It is not a good answer.
Curb Appetite's normalized schema is exactly right for what it's for: taking an order, charging a card, dispatching a driver, without ever risking two rows disagreeing about a fact that should only exist once. It's optimized for writes — specifically, for writes that can never quietly corrupt themselves.
Now someone in ops asks a completely reasonable question: "What's our revenue by cuisine, by city, by month?"
SELECT
r.cuisine,
z.city,
date_trunc('month', o.order_date) AS month,
SUM(oi.quantity * mi.item_price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN menu_items mi ON mi.menu_item_id = oi.menu_item_id
JOIN restaurants r ON r.restaurant_id = o.restaurant_id
JOIN customers c ON c.customer_id = o.customer_id
JOIN zip_codes z ON z.zip = c.zip
GROUP BY r.cuisine, z.city, date_trunc('month', o.order_date)
ORDER BY month, revenue DESC;
Six joins across seven tables, for a question that isn't even asking for anything unusual. This isn't a sign the normalization was done wrong — it's the opposite. Every one of those joins exists precisely because the schema is correctly normalized: cuisine lives with the restaurant, city lives with the zip, price lives with the menu item, none of it duplicated anywhere. Correctness for writes and convenience for reads are different design goals, and a schema optimized entirely for the first will always look like this the moment you ask it to do the second.
Denormalizing on purpose: from 3NF to star schema
The fix isn't to loosen the normalized schema — that would reintroduce the exact update anomalies it exists to prevent, in the system that's still taking live orders. The fix is to build a second, derived schema, populated from the normalized one on a schedule, shaped entirely around the read side. This is a Kimball-style star schema: one fact table at a clearly stated grain, surrounded by denormalized dimension tables, one join away from anything.
First, the grain — the single most important decision in the whole exercise, stated as a sentence before any SQL: one row per item on an order. Not one row per order (too coarse — you'd lose the ability to ask "how did Pad Thai do specifically"), not one row per delivery event (too fine — nothing here needs that granularity).
CREATE TABLE fact_order_line (
order_line_sk BIGSERIAL PRIMARY KEY,
order_id TEXT NOT NULL, -- degenerate dimension
order_date_sk INT NOT NULL REFERENCES dim_date(date_sk),
customer_sk BIGINT NOT NULL REFERENCES dim_customer(customer_sk),
restaurant_sk BIGINT NOT NULL REFERENCES dim_restaurant(restaurant_sk),
menu_item_sk BIGINT NOT NULL REFERENCES dim_menu_item(menu_item_sk),
driver_sk BIGINT NOT NULL REFERENCES dim_driver(driver_sk),
quantity INT NOT NULL,
item_price NUMERIC(6,2) NOT NULL,
line_total NUMERIC(8,2) NOT NULL
);
And the dimensions — each one flattening back together exactly what 3NF just spent three sections pulling apart:
CREATE TABLE dim_customer (
customer_sk BIGSERIAL PRIMARY KEY,
customer_id TEXT NOT NULL,
name TEXT NOT NULL,
email TEXT NOT NULL,
city TEXT NOT NULL, -- denormalized back in from zip_codes
state TEXT NOT NULL,
zip TEXT NOT NULL
);
CREATE TABLE dim_restaurant (
restaurant_sk BIGSERIAL PRIMARY KEY,
restaurant_id TEXT NOT NULL,
name TEXT NOT NULL,
cuisine TEXT NOT NULL
);
CREATE TABLE dim_menu_item (
menu_item_sk BIGSERIAL PRIMARY KEY,
menu_item_id TEXT NOT NULL,
item_name TEXT NOT NULL,
restaurant_id TEXT NOT NULL
);
CREATE TABLE dim_driver (
driver_sk BIGSERIAL PRIMARY KEY,
driver_id TEXT NOT NULL,
name TEXT NOT NULL
-- phone didn't make the cut: a support agent's tool needs it,
-- an analyst asking "which drivers deliver fastest" doesn't.
);
city and state are back on dim_customer, duplicated across every customer in the same zip — exactly the redundancy 3NF removed. That's not a mistake here; it's the point. A dimension table is small relative to the fact table and read far more than it's written, so the storage cost of the duplication is negligible and the join it saves is real, on every single query.
The same question, asked of the star schema
SELECT
r.cuisine,
c.city,
d.month_name,
d.year,
SUM(f.line_total) AS revenue
FROM fact_order_line f
JOIN dim_restaurant r ON r.restaurant_sk = f.restaurant_sk
JOIN dim_customer c ON c.customer_sk = f.customer_sk
JOIN dim_date d ON d.date_sk = f.order_date_sk
GROUP BY r.cuisine, c.city, d.month_name, d.year
ORDER BY d.year, d.month_name, revenue DESC;
Three joins, all one hop, instead of six across seven tables. Nothing about the underlying facts changed — this is the exact same revenue, sliced the exact same way. What changed is which design goal the schema is optimized for, and the query got dramatically simpler because the schema stopped fighting the question.
Common mistakes
Denormalizing the system that's still taking orders. The star schema is a second, derived copy, built by a scheduled job from the normalized source — not a replacement for it. The normalized schema keeps doing what it's good at (safe writes); the star schema is where reads happen.
Treating "denormalized" as "no rules." Grain still has to be chosen and stated as a sentence before any table gets built — "one row per order" instead of "one row per order line" would have silently made the Pad Thai question impossible again, just for a different reason than before.
Forgetting that a price can change. Bangkok Nights raises the Thai Iced Tea price next month. If
dim_menu_itemjust gets updated in place, every historicalfact_order_linerow that references it will appear, on next query, to have been sold at the new price — which is wrong, and silently wrong, for every report touching last quarter. This is a slowly changing dimension problem, and it needs a real answer, not a shrug.Rebuilding the star schema by hand, forever. This whole pipeline — normalized source, transform, star schema — is exactly the kind of thing that belongs in a scheduled, tested job, not a one-off script someone reruns when the numbers look stale.
Takeaways
- Normalization and denormalization aren't opposing philosophies where one side is right — they're answers to two different questions: "how do I write this safely" and "how do I read this quickly."
- 1NF, 2NF, and 3NF each fix one specific kind of redundancy — repeating groups, partial dependency on a composite key, transitive dependency on a non-key column — in that order, because each one assumes the last is already fixed.
- The joins that make a normalized schema painful to query are the same joins that make it safe to write to. That's not a design flaw to route around; it's the tradeoff, made visible.
- A star schema doesn't replace the normalized schema — it's a second, deliberately redundant copy, built for a different job, kept in sync on purpose.
Further reading
If this is the first time you've deliberately denormalized something instead of just being told "star schemas are good, snowflakes are bad," the five-part Kimball series on this profile goes considerably deeper on the read side specifically — grain, star vs. snowflake, slowly changing dimensions (including the exact menu-item-price problem flagged above), accumulating snapshots, bridge tables, and a case with no textbook-correct grain at all. This article is the piece that comes before all of it.
Top comments (0)