Normalization and denormalization

What were the normal forms for, and which of their reasons survives in an append-only analytical store?

What the normal forms say

Normal forms are a theory of functional dependencies. A functional dependency "A determines B" means: if you know A, there is exactly one possible B. (product_id determines product_name.)

Suppose a table order_lines(order_id, line_no, product_id, product_name, category, customer_id) has the key (order_id, line_no).

• 2NF removes dependence on part of a composite key. customer_id depends on order_id alone, so it belongs in an orders table.
• 3NF removes transitive dependence (A determines B, B determines C, so A determines C only through B). line → product_id → product_name is such a chain, so product attributes belong in a products table.
• BCNF (Boyce-Codd normal form) requires that every determinant (the left side of a dependency) is a candidate key, meaning it could serve as a key of the table.

The purpose of all of them is identical: store each fact once, so that changing it touches exactly one row.

Why the purpose no longer applies in analytics

That purpose is conditional on in-place update. In a transactional database, if the category "Tents" is renamed "Shelters" and the name is stored in 40,000 rows, you must update all 40,000 in one transaction, or you get an update anomaly. In an append-only analytical store, nothing is updated in place, so the anomaly does not arise.

The second classical reason was space. It was answered by compression. Worked example. A country column over 1 billion rows with 200 distinct values: as text of about 10 bytes per value it is about 10 GB. With dictionary encoding each value becomes a 1-byte code (200 fits in one byte, since a byte holds 256 values): 1 GB. On data sorted so that equal values sit together, run-length encoding shrinks that to almost nothing. And column pruning means an unused wide column costs nothing to read. (These sizes are illustrations.)

The speed argument for denormalizing was answered by broadcast joins and runtime filters. So the folklore chain "normalize to save space, denormalize for speed" is doubly obsolete: the space term is small and the speed term has been absorbed by the engine.

What survives: a single point of definition

One reason survives, and it is not logical:

That is a lineage property (where a column came from and who computes it), not a schema property. No amount of normal-form checking detects it. Two tables can both be in perfect BCNF and both carry a customer_segment column computed by different rules.

A second survival is educational. Doing the normalization exercise forces you to list the functional dependencies, which is the same activity as finding where one entity ends and the next begins. The output you want is the entity boundaries, not the tables.