Normalization and Denormalization

Normalization is the process of structuring tables to eliminate redundant data and prevent inconsistent updates. Each level ("normal form") builds on the one before it.

The Problem: An Unnormalized Table

CREATE TABLE orders_bad (
  order_id INTEGER,
  customer_name TEXT,
  customer_email TEXT,
  product_names TEXT  -- 'Laptop, Mouse, Keyboard'
);

• The customer's name/email is repeated on every order — if it changes, you must update it everywhere.
• product_names crams multiple values into one field — you can't easily query "how many orders included a Mouse?"

First Normal Form (1NF): Atomic Values

Each column should hold a single, indivisible value — no comma-separated lists crammed into one field.

CREATE TABLE order_items (
  order_id INTEGER,
  product_name TEXT
);
-- one row per product, instead of one comma-separated column

Second Normal Form (2NF): No Partial Dependencies

Every non-key column must depend on the entire primary key, not just part of it (this only matters for tables with a composite key).

-- BAD: customer_name only depends on customer_id, not on the (order_id, product_id) pair
CREATE TABLE order_items_bad (
  order_id INTEGER,
  product_id INTEGER,
  customer_name TEXT,
  PRIMARY KEY (order_id, product_id)
);

-- GOOD: move customer info to its own table, keyed by customer_id
CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name TEXT
);
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id)
);

Third Normal Form (3NF): No Transitive Dependencies

A non-key column shouldn't depend on another non-key column — it should depend only on the primary key.

-- BAD: department_name depends on department_id, not directly on employees.id
CREATE TABLE employees_bad (
  id SERIAL PRIMARY KEY,
  name TEXT,
  department_id INTEGER,
  department_name TEXT  -- redundant; changes to a department's name must be updated everywhere
);

-- GOOD: split into two tables
CREATE TABLE departments (
  id SERIAL PRIMARY KEY,
  name TEXT
);
CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT,
  department_id INTEGER REFERENCES departments(id)
);

When to Denormalize on Purpose

Normalization optimizes for data integrity, sometimes at the cost of extra joins. Denormalization intentionally duplicates data to optimize for read speed — common in reporting/analytics tables (see the Dimensional Modeling lesson), where join-heavy queries would otherwise be too slow.

-- a denormalized reporting table, rebuilt periodically for fast dashboard queries
CREATE TABLE sales_report AS
SELECT o.id, c.name AS customer_name, d.name AS department_name, o.total
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN departments d ON o.department_id = d.id;

Common Errors & Troubleshooting

• Over-normalizing a small, rarely-changing lookup table — splitting out something like a fixed 5-value status enum into its own table adds a join for little real benefit; normalization is a means to an end (data integrity), not a rule to follow blindly everywhere.
• Forgetting to add a foreign key after normalizing — splitting data into a new table without adding a REFERENCES constraint back to it reintroduces the exact inconsistency risk normalization was meant to prevent.
• Denormalizing without a plan to keep it in sync — a denormalized reporting table (or duplicated column) that's never refreshed silently goes stale; pair denormalization with a scheduled refresh or trigger (see the Materialized Views and Incremental Loads lessons).

Key Takeaways

• 1NF requires atomic values; 2NF and 3NF progressively remove dependencies that aren't purely on the primary key.
• Normalization prevents update anomalies (changing one fact in multiple places) at the cost of needing more joins to read the data back.
• Denormalization is a deliberate, informed trade-off for read performance — not a mistake, when applied intentionally in the right place (like a reporting layer).