Normalization

What is it?

Imagine a single orders table that stores the customer's name and email directly on every order row, instead of just a user_id. If that customer changes their email, you'd need to find and update it on every single order they've ever placed — miss one, and now your data disagrees with itself about what their email is.

Normalization is the practice of restructuring tables so each fact is stored exactly once, and everything else references it (usually via a foreign key) instead of copying it. It doesn't eliminate the relationship between orders and customer info — it just makes sure that info lives in one authoritative place.

Explain like I'm 10

Storing a customer's email on every order is like writing a friend's phone number on every single letter you send them. Normalization is like writing their name in your address book once, and just referencing 'call the number in my address book' everywhere else — update it once, and every future reference is correct.

Examples

Before: duplicated data

-- orders table (not normalized)
id | customer_name | customer_email      | total_cents
1  | Amara Musa     | amara@example.com  | 4599
2  | Amara Musa     | amara@example.com  | 1200
3  | Amara Musa     | amara@example.com  | 800

-- Amara's email is repeated on every single order.
-- Changing it means updating all 3 rows, and it's easy to miss one.

The same customer's name and email are copied into every order row — a change to her email requires finding and fixing every one of these rows.

After: normalized into two tables

-- users table
id | name        | email
2  | Amara Musa  | amara@example.com

-- orders table (normalized)
id | user_id | total_cents
1  | 2       | 4599
2  | 2       | 1200
3  | 2       | 800

-- Amara's email now lives in exactly one row.
-- Updating it there instantly applies everywhere it's referenced.

Now Amara's details exist in exactly one row. Every order just references her by user_id, so updating her email means changing a single row, and every order automatically reflects the correct value through a join.

How it works

Normalization is usually described in stages called normal forms, each fixing a specific kind of duplication or inconsistency — for example, making sure a column doesn't hold multiple values at once, or that non-key columns don't depend on only part of a multi-column key. In practice, most everyday schema design just applies the core idea repeatedly: if a fact could change and would require updating more than one row to stay correct, pull it into its own table and reference it with a foreign key instead.

Why does it exist?

Duplicated data is a breeding ground for inconsistency — the same fact stored in ten places will eventually disagree with itself, because someone updates nine of them and misses one. Normalization exists to guarantee that each fact has exactly one authoritative home, so there's never a question of "which copy is correct."

When to use it

Normalize whenever a piece of information logically belongs to one entity (a customer's email, a product's price) and might change over time — keeping it in one place makes updates safe and consistent. This is the default approach for most relational schema design.

When not to use it

Heavily normalized data requires more joins to reassemble, which can cost performance on read-heavy systems. Sometimes a deliberate amount of duplication (denormalization) is accepted for speed — for example, storing a product's name directly on a historical order line item, so an old receipt still shows the product's name at the time of purchase even if the product is later renamed.

Common mistakes

• Storing a repeatable value (like a customer's address) directly on every related row instead of referencing it from a single source table.

• Over-normalizing every last detail, resulting in so many tables that simple queries require deep chains of joins.

• Confusing normalization with just 'having multiple tables' — the goal is eliminating duplicated, update-prone facts, not table count for its own sake.

Practice exercises

1. Easy:

   Explain what could go wrong if a shipping address were duplicated across every order a customer places, instead of stored once.

2. Medium:

   Take a single 'employees' table that repeats department name and department manager on every employee row, and redesign it into two normalized tables.

3. Hard:

   Describe a realistic situation where deliberately keeping some duplicated data (denormalization) would be a reasonable tradeoff.

Interview questions

What problem does normalization solve?

It prevents the same fact from being duplicated across many rows, which would otherwise create the risk of those copies becoming inconsistent with each other.

What's the tradeoff of a highly normalized schema?

Reassembling related data requires more joins, which can add query complexity and cost compared to having some data duplicated.

What is denormalization?

A deliberate decision to duplicate some data (accepting the inconsistency risk) in exchange for faster reads or simpler queries.