Normalization

What normalization is#

Normalization is the process of organizing tables to reduce data duplication and the anomalies that duplication causes — the same fact stored in two places will eventually disagree with itself once one copy gets updated and the other doesn't. Each "normal form" is a progressively stricter rule a table can satisfy; in practice, most schemas are designed straight to third normal form (3NF) without walking through the earlier ones as separate migration steps.

The starting point: an unnormalized table#

Imagine a single table tracking orders, storing everything about the order, the customer, and each product line item all in one row per line item:

order_id | customer_name | customer_email      | product_name        | product_price | quantity
1        | Amara Diallo  | amara@example.com   | Wireless Mouse      | 24.99         | 2
1        | Amara Diallo  | amara@example.com   | Mechanical Keyboard | 89.00         | 1
2        | Liam Chen     | liam@example.com    | Mechanical Keyboard | 89.00         | 1

This single table already reveals the problems normalization exists to fix:

• Update anomaly — if Amara changes her email, it must be updated on every row that mentions her, or the data becomes inconsistent.
• Insertion anomaly — a new product can't be recorded until someone orders it, since there's no place to store product data independent of an order line.
• Deletion anomaly — deleting order 2 (Liam's only order) deletes the only record of Liam's email address entirely, even though "Liam exists as a customer" and "order 2 happened" are logically separate facts.

First normal form (1NF): atomic values, no repeating groups#

1NF requires every column to hold a single, atomic value — no comma-separated lists, no repeating groups of columns for the same kind of data. The table above is already technically in 1NF (no column holds multiple values), but a table that instead stored products: "Wireless Mouse, Mechanical Keyboard" as one comma-separated string would violate it — there'd be no way to query, index, or constrain individual products without parsing that string in application code first.

Second normal form (2NF): no partial dependency on a composite key#

2NF applies to tables with a composite primary key, and requires that every non-key column depend on the entire key, not just part of it. In the unnormalized table above, if the key were (order_id, product_name), then customer_name and customer_email depend only on order_id (not on product_name too) — a partial dependency, which is what 2NF forbids. The fix is splitting customer data out into its own table, keyed by something that actually determines it (a customer id):

CREATE TABLE customers (
    id    INT PRIMARY KEY AUTO_INCREMENT,
    name  VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE
);

Third normal form (3NF): no transitive dependency#

3NF goes further: every non-key column must depend on the primary key directly, not on another non-key column. In the original table, product_price depends on product_name, not on order_id directly — a transitive dependency (order_id → product_name → product_price). The fix is the same idea again: pull products into their own table.

The fully normalized result#

CREATE TABLE customers (
    id    INT PRIMARY KEY AUTO_INCREMENT,
    name  VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE products (
    id    INT PRIMARY KEY AUTO_INCREMENT,
    name  VARCHAR(150) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

CREATE TABLE orders (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE order_items (
    order_id   INT NOT NULL,
    product_id INT NOT NULL,
    quantity   INT NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

Now Amara's email exists in exactly one row, a product's price exists in exactly one row, and none of the three anomalies above are possible anymore. Retrieving the original flattened view is a join away when it's needed for display, which is a small, well-understood cost compared to the data-integrity risk of the unnormalized version.

When denormalization is the right call#

Normalization optimizes for data integrity and avoiding duplication, generally at the cost of needing more joins to reassemble a full picture. That trade-off isn't free, and for specific, well-understood cases, deliberately going back the other way — denormalizing — is the correct engineering decision, not a mistake:

• Read-heavy reporting tables — a nightly job that flattens normalized data into a wide, denormalized reporting table (or materialized view) so a dashboard can run a simple SELECT instead of a five-table join across millions of rows on every page load.
• Historical snapshots — an order_items row storing the product's price at the time of purchase, duplicated from the live products.price, is deliberate and correct denormalization: the order should reflect what the customer actually paid, not whatever the product costs today.
• Extreme read scale — systems serving enormous read volume sometimes trade some normalization for fewer joins per request, accepting the maintenance cost of keeping duplicated data in sync (often via triggers, application logic, or a change-data-capture pipeline) in exchange for lower read latency at scale.

The rule of thumb: normalize by default, and denormalize deliberately, in specific places, for a specific measured reason — not as a blanket alternative to modeling the data properly in the first place.

Common mistakes#

• Treating normalization as a checklist to satisfy for its own sake, rather than understanding why each normal form exists — the anomalies it prevents are the actual point.
• Denormalizing prematurely, before an actual performance problem is measured, and paying the ongoing cost of keeping duplicated data in sync for a benefit that was never necessary.
• Forgetting that a denormalized historical snapshot (like a purchase-time price) is supposed to diverge from the live source over time — accidentally "fixing" it to match the current value defeats its entire purpose.
• Over-normalizing to the point where a simple, common read requires joining six or seven tables together, when a small amount of deliberate denormalization for that specific read pattern would be a reasonable trade-off.