Normalization vs Denormalization: Stop Wasting Queries and Start Designing Sane Databases

// io.thecodeforge — System Design tutorial

-- Normalized schema: each fact stored once
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    address VARCHAR(200)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),
    order_date DATE
);

-- Query: get customer name and order date
SELECT c.name, o.order_date
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id = 123;

-- Output:
-- name       | order_date
-- Jane Doe   | 2024-03-15

// io.thecodeforge — System Design tutorial

-- Denormalized product table for read-heavy product pages
CREATE TABLE product_details (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category_name VARCHAR(50),       -- denormalized from categories table
    average_rating DECIMAL(2,1),      -- denormalized from reviews table
    review_count INT,                 -- denormalized for quick display
    last_updated TIMESTAMP            -- track staleness
);

-- Read query: single table, no JOIN
SELECT product_name, category_name, average_rating
FROM product_details
WHERE product_id = 456;

-- Write: updating a rating requires updating all products with that rating? No, only this row.
-- But if category name changes, you must update every product in that category.
UPDATE product_details
SET category_name = 'Electronics'
WHERE category_name = 'Gadgets';  -- O(n) update, could be slow

// io.thecodeforge — System Design tutorial

-- Normalized source tables
CREATE TABLE orders (order_id INT, customer_id INT, total DECIMAL);
CREATE TABLE customers (customer_id INT, name VARCHAR(100));

-- Denormalized materialized view for fast reporting
CREATE MATERIALIZED VIEW order_summary AS
SELECT o.order_id, c.name AS customer_name, o.total
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

-- Refresh periodically (e.g., every 5 minutes)
REFRESH MATERIALIZED VIEW order_summary;

-- Query the view: no JOIN, fast
SELECT * FROM order_summary WHERE order_id = 789;

-- Output:
-- order_id | customer_name | total
-- 789      | Alice Smith   | 150.00

// io.thecodeforge — System Design tutorial

-- Bad: denormalized customer name in orders
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    customer_name VARCHAR(100),  -- denormalized, must stay in sync
    total DECIMAL
);

-- When customer changes name:
UPDATE orders
SET customer_name = 'New Name'
WHERE customer_id = 42;
-- This locks all rows for customer 42, blocking other writes.

-- Better: normalized write path
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    total DECIMAL
);

-- Read: JOIN with cache
SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_id = 123;
-- Use Redis to cache customer name for 5 minutes.

// io.thecodeforge — System Design tutorial

-- Normalized: index foreign keys
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_order_date ON orders(order_date);

-- Denormalized: index filter columns
CREATE INDEX idx_product_details_category ON product_details(category_name);

-- Query that benefits from index on category_name
SELECT product_name, average_rating
FROM product_details
WHERE category_name = 'Electronics'
ORDER BY average_rating DESC;

-- Output:
-- product_name       | average_rating
-- Wireless Mouse     | 4.5
-- Bluetooth Speaker  | 4.2

// io.thecodeforge — System Design tutorial

-- Denormalized log table: no updates, only inserts
CREATE TABLE access_logs (
    log_id BIGINT AUTO_INCREMENT,
    timestamp TIMESTAMP,
    user_id INT,
    user_name VARCHAR(100),   -- denormalized, but user name rarely changes
    action VARCHAR(50),
    resource VARCHAR(100),
    ip_address VARCHAR(45),
    PRIMARY KEY (log_id),
    INDEX idx_timestamp (timestamp)
);

-- Insert: no JOIN needed
INSERT INTO access_logs (timestamp, user_id, user_name, action, resource, ip_address)
VALUES (NOW(), 42, 'Jane Doe', 'VIEW', '/dashboard', '192.168.1.1');

-- Query: fast single table scan with index
SELECT user_name, action, resource
FROM access_logs
WHERE timestamp > NOW() - INTERVAL 1 HOUR;

-- Output:
-- user_name | action | resource
-- Jane Doe  | VIEW   | /dashboard

// io.thecodeforge — System Design tutorial

-- Pseudocode: cache-aside pattern in application
function getOrderSummary(orderId) {
    cacheKey = "order_summary:" + orderId;
    result = redis.get(cacheKey);
    if (result != null) return result;

    // Normalized query with JOIN
    result = db.query("SELECT o.order_id, c.name, o.total " +
                      "FROM orders o JOIN customers c ON o.customer_id = c.customer_id " +
                      "WHERE o.order_id = ?", orderId);

    redis.setex(cacheKey, 300, result);  // TTL 5 minutes
    return result;
}

// Output:
// { order_id: 123, name: "Jane Doe", total: 150.00 }