Normalize or denormalize data: when does each pay off?

Normalizing means organizing data into separate tables so that each piece of information lives in exactly one place: a customer's address sits in the customers table, not repeated in every one of their orders. The benefit is integrity: if the address changes, you update it in one spot and every view reflects it, with no risk of two copies drifting apart. Denormalizing does the opposite on purpose: deliberately duplicating information across tables to avoid runtime joins, accepting that the same piece of information lives in more than one place. Normal forms (first, second, third, and beyond) are the formal rungs database theory uses to describe how far normalization goes, but in everyday practice few teams reason in those terms: the concrete question always stays the same, where you need integrity and where you need read speed.

Where each one wins

A transactional system (OLTP), the ERP processing orders in real time, wants normalization: every write touches a single place, no risk of inconsistency between duplicate copies, and the row volume per transaction is low so joins are cheap. An analytical system (OLAP) pushes the other way, though less absolutely than it once did: on modern columnar engines a join between a large fact table and small dimensions is cheap, because the dimension is broadcast to the nodes and used to filter the scan. Denormalizing pays off when the joins are many, when both sides are large, or when the same query runs thousands of times a day; and it carries its own cost, since a wider table means more bytes to scan. I settle it by measuring on the engine in use, never a priori.

The practical rule

The rule that holds across most modern data architectures is simple: normalize the source of truth, denormalize the serving layer. The operational system and the integrated layer of the data warehouse stay normalized, because integrity matters more than read speed there; the gold layer built for BI and dashboards gets denormalized on purpose, because read speed matters more than model purity there. Applying the same rule everywhere, in either direction, is almost always the mistake: the right trade-off depends on who reads that data and how often.