Normalization is how you stop the same fact from living in two cells that will disagree next Tuesday. Codd’s normal forms are rules about functional dependency, not a religion that forbids denormalized read models. OLTP databases want high normal form; analytics and caches often do not.
The forms you actually use
- 1NF: atomic cells, no repeating groups. “phone1, phone2” is a list pretending to be columns — use a
phonetable or a real array type with a documented meaning. - 2NF: non-key attributes depend on the whole key. In a composite key (order_id, line_no), customer name does not belong on the line.
- 3NF: no transitive dependency. City name determined by postcode should not also sit on every customer row unless you accept the update anomaly, and only when that dependency actually holds (a postcode does not name one city everywhere).
- BCNF: every determinant is a superkey (a candidate key, or a superset of one). The one that bites when you have overlapping unique keys.
Higher forms (4NF, 5NF) show up with multi-valued facts. 4NF is two independent multi-valued facts in one row, such as a person with several skills and several languages. A comma-separated tag list is a different bug, a 1NF repeating group. Most product schemas never name 4NF and still have that tag column.
Update, insert, delete anomalies
If renaming a customer requires rewriting 40 order rows, you are denormalized in a place that is still written as source of truth. That is the bug. A read replica or a search document that repeats the name is a projection you can rebuild.
When to denormalize
Hot read paths, event-sourced projections, full-text indexes. Write down the source of truth and how you refresh. Do not denormalize “for performance” without measuring, then skip the unique constraint on the source table.
Example
Customer name depends on the order, not on the line. Renaming the customer is one row.
order_line(order_id, line_no, customer_name, sku)
order(order_id, customer_name)
order_line(order_id, line_no, sku)What breaks it
phone1 and phone2 are a repeating group. A third number has nowhere to go. Use a phone row, or one array whose meaning is documented.
customer(id, phone1, phone2)
phone(customer_id, number)Pitfalls
- JSON columns as a junk drawer with no constraints.
- Spreading a 3NF schema across microservices without a foreign key — you still have a dependency, just a broken one.