Normalize It, Then Break It On Purpose: 3NF to Star Schema, Explained Through Food Delivery

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:

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