Database Normalization: When to Normalize and When to Denormalize

Normalization is one of those topics that sounds academic until you’re debugging why your data is inconsistent at 2 AM. Let’s make it practical.

The Problem Normalization Solves

Imagine storing orders like this:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100),
    customer_email VARCHAR(100),
    customer_address TEXT,
    product_name VARCHAR(100),
    product_price DECIMAL(10,2),
    quantity INT
);

What happens when a customer updates their email? You need to update every row they’ve ever ordered. Miss one? Now you have inconsistent data.

First Normal Form (1NF)

Rule: No repeating groups. Each column contains atomic values.

-- ❌ Bad: Multiple values in one column
CREATE TABLE orders (
    id INT,
    products TEXT  -- "Widget, Gadget, Gizmo"
);

-- ✅ Good: One value per cell
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT
);

Second Normal Form (2NF)

Rule: Every non-key column depends on the entire primary key.

-- ❌ Bad: product_name depends only on product_id, not order_id
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    product_name VARCHAR(100),  -- Depends only on product_id
    PRIMARY KEY (order_id, product_id)
);

-- ✅ Good: Split into separate tables
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE order_items (
    order_id INT,
    product_id INT REFERENCES products(id),
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

Third Normal Form (3NF)

Rule: No transitive dependencies. Non-key columns shouldn’t depend on other non-key columns.

-- ❌ Bad: city depends on zip_code, not directly on customer_id
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    zip_code VARCHAR(10),
    city VARCHAR(100)  -- Determined by zip_code
);

-- ✅ Good: Separate the dependency
CREATE TABLE zip_codes (
    code VARCHAR(10) PRIMARY KEY,
    city VARCHAR(100)
);

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    zip_code VARCHAR(10) REFERENCES zip_codes(code)
);

When to Denormalize

Here’s the twist: sometimes normalization hurts performance.

Read-Heavy Workloads

If you’re constantly joining tables:

-- This join happens on every page load
SELECT o.*, c.name, c.email
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.id = 123;

Consider caching frequently-accessed data:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(id),
    customer_name VARCHAR(100),  -- Denormalized for read speed
    customer_email VARCHAR(100), -- Updated via triggers or app logic
    -- ...
);

Reporting and Analytics

Normalized schemas are terrible for analytics. That’s why data warehouses use star schemas with denormalized fact tables.

Historical Data

When an order ships, you want to preserve the address at that moment:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(id),
    shipping_address TEXT,  -- Snapshot, not a reference
    -- ...
);

Practical Guidelines

1. Start normalized. It’s easier to denormalize later than to normalize a mess.

2. Denormalize for specific performance problems. Don’t guess—measure.

3. Use views for convenience without duplication:

   CREATE VIEW order_details AS
   SELECT o.*, c.name as customer_name, c.email as customer_email
   FROM orders o
   JOIN customers c ON o.customer_id = c.id;
   

4. Document your denormalizations. Future you will thank present you.

5. Consider materialized views for expensive queries that don’t need real-time data.

The Balance

Normalization prevents anomalies. Denormalization improves read performance. The best database design acknowledges both.

For OLTP (transactional) systems: lean toward normalization. For OLAP (analytical) systems: denormalize aggressively.

For most applications: normalize by default, denormalize surgically where profiling shows it matters.