Database Normalization

Database normalization organizes relations around dependencies to reduce avoidable repetition and anomalies. If an order-line table repeats the customer’s current city, a move requires many updates; missing one produces inconsistent current values. Separating customer state from order facts addresses that dependency.

In an introductory relational design, first normal form avoids repeating groups and multivalued cells. Second normal form additionally requires each non-prime attribute to depend on the whole of every candidate key, not a proper subset. Non-prime means belonging to no candidate key. Adding a surrogate primary key alone does not remove dependencies on other candidate keys.

Third normal form requires each nontrivial dependency X → A to have X as a superkey or A as a prime attribute. A superkey uniquely identifies a row, and a prime attribute belongs to at least one candidate key. The familiar advice to remove transitive non-key dependencies is a useful introductory case, not the full definition. A shipping city or price agreed for an order is an order fact, not necessarily a duplicate of a customer’s current city or a product’s current price. Preserve meaning before decomposing tables; transactions and constraints are still needed for correct writes.

Normalization answers a question that belongs to systems that write: how to keep a value from being stored in two places where one copy can fall behind. Analytical models routinely accept the redundancy it removes — a product dimension repeats the category on every row instead of pointing at a category table — and that is not a lapse. The trade is different because the writes are different: a dimension is loaded by a controlled process rather than edited by many concurrent users, so the anomaly normalization prevents is handled by the load rather than by the structure, and what is bought in return is that a reader does not have to join five tables to label a row.

The distinction that survives both settings is the one above: repeating a value that can change independently is a hazard, and recording a value as it stood at an event is a fact. A dimension that repeats a category is storing a label; a dimension that repeats a customer’s current address as though it were the address used for a past shipment is storing a hazard, whatever its normal form.

Further reading

• Microsoft Support, Description of the database normalization basics

See the worked examples in Data Modeling for Operational and Analytical Systems.