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.

Reference: Microsoft normalization basics. See Data Modeling for Operational and Analytical Systems for the worked example.