Data Modeling & Normalization
1NF through 3NF, and why your data ends up split across multiple tables in the first place.
Overview
NormalizationOrganizing a schema so each real-world fact is stored in exactly one place, via a series of normal forms (1NF, 2NF, 3NF) — prevents duplicated data from silently drifting out of sync.Learn more is the process of organizing a schema so that each fact about the world is stored in exactly one place. It's formalized as a series of normal forms — 1NF, 2NF, 3NF, and beyond — each one closing off a specific way duplicated data can sneak into your tables. You don't need to memorize the formal definitions to use this well; you need to recognize the failure mode it's preventing: the same fact, copied across many rows, quietly drifting out of sync with itself.
Why It Exists
Picture an `orders` table with a `customer_name` and `customer_email` column directly on every order row. That looks convenient — one table, no joins — until that customer changes their email. Now you have to find and update every single order row they've ever placed, and if you miss even one, your data is silently self-contradictory: the same customer has two different emails depending on which row you look at. Normalization exists to make that class of bug structurally impossible, by storing each fact in exactly one row, in exactly one table.
Real World Example
Take that same `orders` table storing `customer_name` and `customer_email` directly. A normalized version splits it into two tables: a `customers` table holding `id`, `name`, and `email`, and an `orders` table holding `id`, `customer_id`, `product`, and `price` — where `customer_id` points at a row in `customers` instead of repeating that customer's details. Now, when the customer updates their email, you change exactly one row, in one table, and every order that references them reflects the update automatically, because nothing about the customer's identity was ever duplicated in the first place.
Example Data
Before — orders (unnormalized): the customer's name and email are duplicated on every row
| id | customer_name | customer_email | product | price |
|---|---|---|---|---|
| 1 | Sarah Johnson | sarah.johnson@example.com | Laptop | 1200 |
| 2 | Sarah Johnson | sarah.johnson@example.com | Mouse | 25 |
| 3 | Michael Carter | michael.carter@example.com | Keyboard | 80 |
After — customers: each customer's details stored exactly once
| id | name | |
|---|---|---|
| 1 | Sarah Johnson | sarah.johnson@example.com |
| 2 | Michael Carter | michael.carter@example.com |
After — orders (normalized): references a customer by id instead of repeating their details
| id | customer_id | product | price |
|---|---|---|---|
| 1 | 1 | Laptop | 1200 |
| 2 | 1 | Mouse | 25 |
| 3 | 2 | Keyboard | 80 |
How It Works
1NF (First Normal Form) requires every column to hold a single, atomic value — no comma-separated lists jammed into one field, no repeating groups of columns like `product1`, `product2`, `product3`. 2NF builds on that by requiring every non-key column to depend on the whole primary key, not just part of it — this only bites when a table has a composite key, like an `order_items` table keyed on `(order_id, product_id)`, where a column like `product_name` actually depends only on `product_id`, not the pair. 3NF goes further: every non-key column must depend on the key and nothing but the key — no non-key column should depend on another non-key column, the way `customer_email` in the original `orders` example depended on `customer_name`, not on the order itself. In practice, chasing 3NF is what naturally splits one wide table into several narrow, related ones.
Diagram
Splitting a table by dependency — each fact ends up stored exactly once
Unnormalized: orders
id, customer_name, customer_email, product, price
customers
id, name, email
orders
id, customer_id, product, price
Common Mistakes
Duplicating the same real-world fact across many rows, like a customer's email on every one of their orders
Why: Every duplicate copy is a chance for that fact to drift out of sync — update one copy and miss another, and the data becomes silently self-contradictory.
Fix: Store each fact once, in the table it actually belongs to, and reference it by key from anywhere else it's needed.
Normalizing so aggressively that a common read requires joining six or seven tables
Why: Correctness on paper doesn't help if every dashboard query now pays for a pile of joins that measurably slow the application down.
Fix: Normalize by default, then denormalize specific, measured hot paths on purpose — this is the well-known 'normalize until it hurts, denormalize until it works' approach.
Splitting tables without checking whether the split actually removes a real dependency
Why: Adding tables for their own sake adds join complexity without the correctness benefit normalization is supposed to buy you.
Fix: Before splitting a table, name the specific fact that was duplicated or the specific dependency being removed — if you can't name it, the split isn't normalization.
Interview Questions
In plain terms, what problem does normalization solve?
It stops the same real-world fact from being copied across multiple rows, which prevents that fact from silently going out of sync when only some of the copies get updated.
What's the practical difference between 2NF and 3NF?
2NF requires a non-key column to depend on the entire primary key, which only matters when the key is composite — like `(order_id, product_id)`. 3NF is broader: it requires every non-key column to depend only on the key, not on another non-key column — like `customer_email` depending on `customer_name` instead of on the order itself, which is what forces splitting customer data into its own table.
When would you deliberately denormalize a schema, and how do you stop it from becoming inconsistent?
When a specific, measured read path is too slow because of join cost — usually a reporting or dashboard query — and the duplicated data changes rarely enough that keeping it in sync is manageable. The key is making the duplication explicit and owned: pick one mechanism (a scheduled job, a trigger, or Change Data Capture) that's responsible for propagating updates to the denormalized copy, instead of leaving every part of the codebase to remember to update it.
Production Best Practices
Do
✓Normalize by default — store each fact in exactly one place unless you have a measured reason not to.
✓Name the specific dependency you're removing whenever you split a table.
✓Denormalize deliberately, for a specific measured hot path, with one clear owner for keeping the copy in sync.
Don't
✗Don't duplicate a fact like a customer's contact details across every row that references them.
✗Don't normalize past the point of diminishing returns without checking the real query cost first.
✗Don't denormalize 'just in case' — only do it once a specific read path is provably too slow.
Comparison
| Removes | Typical Fix | |
|---|---|---|
| 1NF | Non-atomic values, repeating column groups | Split one column of many values into one row per value |
| 2NF | Column depending on only part of a composite key | Move that column to the table matching its real key |
| 3NF | Column depending on another non-key column | Move that column to its own table, referenced by key |