Normalization is a stack of rules for organizing tables to avoid redundancy. The textbook covers 1NF through 5NF. In practice, you'll meet three of them and the rest live in academic papers.
1NF — atomic values, no repeating groups
Rule: each cell holds one value; there are no comma-separated lists, no repeating column groups.
Bad (violates 1NF):
orders(order_id, customer, items)
1, Anuj, "shoes, hat, belt"
Good (1NF):
order_items(order_id, item)
1, shoes
1, hat
1, belt
1NF is non-negotiable in any relational system. Every column has one value. Move on.
2NF — no partial dependencies on a composite key
Rule: every non-key column depends on the full primary key, not just part of it.
Bad (violates 2NF when PK is (order_id, item)):
order_items(order_id, item, customer_name, item_price)
customer_name depends on order_id alone (not on item). item_price depends on item alone. Split:
order_items(order_id, item, quantity) -- both columns needed for quantity
items(item, item_price) -- price depends on item only
orders(order_id, customer_name) -- customer depends on order only
2NF only matters when you have composite primary keys. Most modern OLTP tables have a single surrogate primary key (auto-increment or UUID), which sidesteps 2NF entirely.
3NF — no transitive dependencies
Rule: non-key columns depend on the key, the whole key, and nothing but the key.
Bad (violates 3NF):
employees(emp_id, department_id, department_name, department_manager)
department_name and department_manager depend on department_id, not on emp_id. The fix is to split:
employees(emp_id, department_id)
departments(department_id, department_name, department_manager)
3NF is the working standard for OLTP databases. It prevents update anomalies (updating a department name in 50 employee rows when you should update it in one departments row) and ensures consistency.
When 3NF helps
- High write volume — updating one row beats updating 100.
- Data integrity matters — FK constraints ensure references are valid.
- Storage is expensive — denormalized tables are large.
- Schema changes are frequent — small focused tables change in isolation.
This is OLTP territory. Your app database lives here.
When to break normalization (in the warehouse)
- Read volume dominates write volume — warehouses load nightly or in batches; reads happen all day.
- Queries are wide aggregations — joining 15 normalized tables for one dashboard query is expensive.
- Engine handles redundancy gracefully — columnar compression makes denormalized columns cheap.
- Dimensional model needs context — the order_fact wants the customer_name right there, not five joins away.
This is OLAP territory. Your warehouse lives here.
The honest answer: "denormalized, deliberately"
A good warehouse model isn't anti-normalization. It's a deliberate denormalization — pre-joining the dimensions you need so analytics queries skip the joins. The Kimball star schema (next module) is the formal name for this approach.
A bad warehouse takes the OLTP 3NF tables verbatim. A good warehouse takes the OLTP data and re-models it.
The 3 questions to ask before denormalizing
- Will the joined columns be queried together >80% of the time? If yes, denormalize.
- Does the dimension table grow much faster than I need? If a "user" dimension is updated every login, copying it into every fact row blows up storage. Use SCDs (next module).
- Will updates to the dimension propagate cleanly? Denormalized data needs a refresh plan. Either accept staleness or rebuild the affected facts.
Common normalization mistakes
- Over-normalizing the warehouse — 14-table joins for every dashboard query.
- Under-normalizing the OLTP — storing JSON blobs that should be tables, leading to update anomalies.
- Treating denormalization as "remove all joins" — some joins are still right (highly volatile dimensions, mostly-unused contexts).
Takeaway
3NF in OLTP, dimensional/denormalized in OLAP. Two databases, two design philosophies, one consistent rule: model for the workload, not for the textbook.