What is Database Normalization?

Normalization is the process of structuring tables to minimize data duplication and the inconsistencies it causes. It's usually explained through a progression of normal forms, each fixing a specific kind of problem left by the one before it.

The unnormalized starting point

orders +----+---------------+------------------------+----------+ | id | customer_name | customer_email | product | +----+---------------+------------------------+----------+ | 1 | Ada Lovelace | ada@example.com | Widget | | 2 | Ada Lovelace | ada@example.com | Gadget | +----+---------------+------------------------+----------+

Ada's name and email are duplicated across every order she places. This isn't just wasted space — it's an update anomaly waiting to happen: change Ada's email in one row and miss the other, and the table now silently disagrees with itself about what her email actually is.

First Normal Form (1NF): atomic values, no repeating groups

Each column must hold a single, indivisible value — no comma-separated lists crammed into one cell:

-- Violates 1NF | id | products | | 1 | Widget, Gadget | -- Satisfies 1NF — one product per row | id | product | | 1 | Widget | | 1 | Gadget |

Second Normal Form (2NF): no partial dependency on a composite key

Applies to tables with a composite primary key: every non-key column must depend on the entire key, not just part of it. If order_items has a composite key of (order_id, product_id), a column like product_name depends only on product_id, not on the pair — so it doesn't belong in order_items at all, it belongs in a separate products table.

Third Normal Form (3NF): no transitive dependency

Every non-key column must depend on the primary key directly, not on another non-key column. This is exactly the orders example above: customer_email depends on customer_name, which depends on the order's id — a chain, not a direct dependency. The fix is the same pattern from the very first lesson of this course: split customers into its own table, and reference it from orders with a foreign key.

customers orders +----+--------+-------------+ +----+-------------+----------+ | id | name | email | | id | customer_id | product | +----+--------+-------------+ +----+-------------+----------+ | 1 | Ada | ada@ex.com | | 1 | 1 | Widget | +----+--------+-------------+ | 2 | 1 | Gadget | +----+-------------+----------+

Now Ada's email exists in exactly one place. Update it once, and every order that references her stays consistent automatically — there's nowhere else for stale data to hide.

Normalization is a trade-off, not an absolute rule

Most application databases target 3NF and stop there — it eliminates the worst duplication problems without excessive splitting. Highly normalized schemas reduce redundancy but require more joins to reassemble a full picture (a customer's name now requires joining orders to customers), which costs query performance. Some systems deliberately denormalize — reintroducing some duplication on purpose — for read-heavy workloads like reporting or analytics, where join cost matters more than the risk of update anomalies on data that rarely changes. Knowing the normal forms means knowing what trade-off you're making, in either direction.

The next lesson covers constraints — the mechanisms, beyond primary and foreign keys, that a database uses to actually enforce a schema's rules.