When I first learned about database normalization, I was intrigued! Every table gets a single purpose. No duplicate data. Everything referenced by foreign keys. I went hard normalizing everything, and my schemas were beautiful, on paper!
Then I tried to query them...
Joining seven tables to get a customer's order history is a special kind of pain. The database groaned. My queries crawled. And I started wondering, did I go overboard with it?
What Normalization Actually Is
Normalization means breaking data into smaller, related tables to eliminate redundancy. Instead of storing the customer name on every order, you store it once in a customers table and reference it by ID.
-- Normalized
orders: id, customer_id, product_id, quantity
customers: id, name, email
products: id, name, price
Benefits: no duplicate data, one place to update, consistent integrity.
Costs: JOINs. So many JOINs meaning immense lagging when it comes to queries.
What Denormalization Actually Is
Denormalization is the opposite, you duplicate data intentionally to make it read faster.
-- Denormalized
orders: id, customer_id, customer_name, product_id, product_name, quantity, price
Benefits: one table, no JOINs, fast reads.
Costs: if a customer changes their name, you have to update every order they've ever made.
The Trade-Off
Normalization optimizes for writes — you update one row and it's done everywhere.
Denormalization optimizes for reads — you query one table and you're done.
There's no right answer. There's only: what does your application do more of?
Transactional systems (placing orders, updating profiles) benefit from normalization. You want fast, safe writes and data integrity.
Analytical systems (reports, dashboards) benefit from denormalization. You want fast reads across millions of rows, and you control when and how data is refreshed.
The Real World Compromise
Most production systems live somewhere in the middle. You keep your operational database normalized for day-to-day transactions. Then you build a denormalized data warehouse for analytics, and you bridge them with an ETL pipeline.
That's what makes the ETL pattern so powerful. Your normalized data source stays clean and consistent. The transformation step denormalizes it into whatever shape your reporting needs. Both worlds get what they want.
A Simple Rule
Normalize until it hurts. Denormalize until it works.
Start with a clean, normalized schema. When a query starts hurting, when you're JOINing six tables in a report that runs every five minutes, denormalize that specific case. Pre-join the data. Cache it. Build that summary table.
Don't denormalize everything preemptively. But don't be a purist either. The best database design is the one your application can actually use.
Top comments (0)