# Normalization vs Denormalization in SQL: A Deep Dive

A deep dive into 1NF/2NF/3NF with real update/insert/delete anomalies, plus when and how to denormalize using duplicated columns, materialized views, and star schemas.

- Author: SqlInt team
- Published: 2026-07-05T12:00:00+00:00
- Updated: 2026-09-08T10:11:42.524791+00:00
- Canonical: https://sqlint.com/articles/normalization-vs-denormalization
- Category: Database Internals
- Tags: sql-normalization, sql-denormalization, database-design

---

## Why This Tradeoff Sits at the Center of Schema Design

Normalization and denormalization represent two opposing goals: data integrity and simplicity versus read performance. Nearly every real production schema is a deliberate mix of both — normalized where correctness matters most, denormalized where read speed matters most. Understanding the actual anomalies normalization prevents (not just the rule names) is what turns this from a memorized definition into real design judgment.

## The Problem: An Unnormalized Table

-- orders: everything jammed into one wide table
| order_id | customer_name | customer_email      | product_name | product_price | quantity |
|----------|-----------------|------------------------|----------------|-----------------|----------|
| 1        | Alice Chen      | alice@example.com    | Widget A     | 25.00           | 2        |
| 2        | Alice Chen      | alice@example.com    | Widget B     | 40.00           | 1        |
| 3        | Bob Diaz        | bob@example.com      | Widget A     | 25.00           | 3        |

This single table looks convenient, but it hides three classic problems:

  - Update anomaly — if Alice changes her email, you have to update every row she appears in; miss one, and her data is now inconsistent within itself.

  - Insert anomaly — you can't add a new product to the catalog until someone actually orders it, since product data only exists attached to an order row.

  - Delete anomaly — if Bob's only order is deleted, his customer record (name, email) disappears from the database entirely, even though he still exists as a customer.

## Normal Forms: What Each One Actually Fixes

### 1NF — Atomic Values, No Repeating Groups

-- Violates 1NF: multiple values crammed into one column
| order_id | products                  |
|----------|---------------------------|
| 1        | Widget A, Widget B        |

-- 1NF-compliant: one row per fact
| order_id | product      |
|----------|---------------|
| 1        | Widget A      |
| 1        | Widget B      |

1NF fixes the problem of not being able to query, filter, or join on individual values buried inside a combined field.

### 2NF — No Partial Dependency on a Composite Key

-- Violates 2NF: product_price depends only on product_id, not the full (order_id, product_id) key
| order_id | product_id | product_price | quantity |

-- 2NF-compliant: split product_price into its own table, keyed by product_id alone
-- products: product_id, product_price
-- order_items: order_id, product_id, quantity

2NF fixes redundant storage of a fact (the price) that doesn't actually depend on the whole composite key — it depends only on part of it, so it's split out to avoid repeating and risking inconsistency across every order line.

### 3NF — No Transitive Dependency

-- Violates 3NF: customer_city depends on customer_id, not directly on order_id
| order_id | customer_id | customer_city |

-- 3NF-compliant: customer_city moves into the customers table, referenced by customer_id
-- orders: order_id, customer_id
-- customers: customer_id, customer_city

3NF fixes the update anomaly directly — a customer's city now lives in exactly one place, so changing it once updates it everywhere that customer is referenced, rather than requiring a multi-row update.

## The Fully Normalized Result

customers:    customer_id, name, email
products:     product_id, name, price
orders:       order_id, customer_id, order_date
order_items:  order_id, product_id, quantity

Every fact now lives in exactly one place. This is the target most production schemas aim for by default — but it comes at a real cost: retrieving a full order's details now requires JOINing four tables instead of reading one.

## Why and When to Denormalize

Denormalization deliberately reintroduces redundancy to reduce the number of JOINs a read-heavy query needs, trading write complexity and potential inconsistency for read speed. It's a reasonable, deliberate tradeoff — not a mistake — under specific conditions:

  - Read-heavy, write-light data — reporting tables, dashboards, and analytics workloads that are queried constantly but updated rarely

  - Performance-critical paths where a JOIN's cost is measured and proven to matter, not assumed

  - Data warehousing — analytical (OLAP) systems commonly use deliberately denormalized star schemas, in contrast to the normalized (OLTP) schemas used for transactional systems

## Common Denormalization Techniques

-- Technique 1: duplicate a frequently-needed column to avoid a join
ALTER TABLE order_items ADD COLUMN product_name VARCHAR(255);
-- Now reporting queries can read product_name directly, without joining products

-- Technique 2: pre-computed summary/aggregate column
ALTER TABLE customers ADD COLUMN lifetime_order_count INT DEFAULT 0;
-- Updated via trigger or application code whenever an order is placed, avoiding a COUNT(*) join at read time

-- Technique 3: a materialized view — a physically stored, periodically refreshed query result
CREATE MATERIALIZED VIEW customer_order_summary AS
SELECT c.customer_id, c.name, COUNT(o.id) AS order_count, SUM(o.total) AS lifetime_spend
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;
-- Refreshed on a schedule (REFRESH MATERIALIZED VIEW), trading data freshness for read speed

## Star Schema: Denormalization in Data Warehousing

Analytical systems commonly organize data into a star schema — one central fact table (orders, events, transactions) surrounded by denormalized dimension tables (customer, product, date) that are deliberately flattened for fast, simple joins in reporting tools.

-- fact_sales: one row per sale, mostly foreign keys and numeric measures
fact_sales: sale_id, date_id, customer_id, product_id, quantity, revenue

-- dim_product: deliberately denormalized — category name repeated on every product row
-- rather than normalized into a separate categories table, since this dimension is
-- rarely updated and simplicity/speed matters more than eliminating redundancy here
dim_product: product_id, product_name, category_name, brand_name

This is the clearest real-world illustration of the tradeoff: OLTP systems (the application database handling live orders) stay normalized for write integrity, while the OLAP/reporting system built on top of that data is deliberately denormalized for query simplicity and speed.

## Common Mistakes

  - Denormalizing prematurely, before actually measuring that JOINs are a real performance problem — adds write complexity and consistency risk for a problem that may not exist yet.

  - Denormalizing without a plan to keep copies in sync — a duplicated column (like product_name above) that's never updated when the source changes silently drifts out of accuracy over time.

  - Over-normalizing a purely analytical/reporting schema — applying OLTP-style normalization rigor to a data warehouse just adds unnecessary JOIN complexity where read speed and simplicity matter more.

  - Confusing "denormalized" with "no constraints" — denormalization is about controlled, deliberate redundancy; it doesn't mean abandoning data integrity rules entirely.

## Common Interview Questions

  - What problem does 3NF actually solve? Transitive dependencies — facts that depend on a non-key column rather than directly on the primary key, which cause update anomalies when duplicated across many rows.

  - When would you deliberately denormalize a table? When a table is read far more often than written, and JOIN cost is measured to be a genuine performance bottleneck — reporting tables and data warehouses are the classic case.

  - What's the difference between a materialized view and a regular view? A regular view is just a stored query, re-executed every time it's read; a materialized view physically stores its result and must be refreshed, trading freshness for speed.

  - Why do data warehouses often use denormalized star schemas while transactional databases stay normalized? OLTP systems prioritize write integrity and avoiding anomalies; OLAP/reporting systems prioritize simple, fast reads across large volumes of historical data — different workloads, different tradeoffs.

## Frequently Asked Questions

### Is 3NF always the right target for a production schema?

It's a strong, safe default for transactional (OLTP) systems, but not a universal rule — some teams deliberately stop at a "good enough" normalization level and denormalize specific hot paths once real performance data justifies it, rather than chasing textbook-perfect 3NF everywhere.

### Does denormalization always mean duplicating data?

Usually, yes, in some form — whether that's a duplicated column, a precomputed aggregate, or a materialized view. The common thread is trading some write-time complexity and potential staleness for read-time simplicity and speed.

## Practice Schema Design Tradeoffs

Normalization and denormalization decisions are where SQL knowledge becomes real database design judgment — the kind senior and staff-level interviews specifically probe for. Try it hands-on with our SQL practice questions, or work through full schema design scenarios in our case studies. For the constraint types that enforce integrity in a normalized schema, see our SQL constraints guide.

---

Source: https://sqlint.com/articles/normalization-vs-denormalization