1NF, 2NF, 3NF Explained: Database Normalization With Real Examples

-- ============================================================
-- BEFORE 1NF: The broken table
-- Problems:
--   1. 'ordered_items' column stores multiple values in one cell
--   2. No single-column primary key — rows aren't cleanly unique
-- ============================================================

CREATE TABLE orders_broken (
    order_id      INT,
    customer_name VARCHAR(100),
    ordered_items VARCHAR(255),   -- BAD: 'Burger, Fries, Soda' in one cell
    item_prices   VARCHAR(255)    -- BAD: '5.99, 2.49, 1.99' matched by position
);

INSERT INTO orders_broken VALUES
    (1, 'Alice Chen',  'Burger, Fries, Soda', '5.99, 2.49, 1.99'),
    (2, 'Bob Martins', 'Pizza, Soda',          '8.99, 1.99');

-- Try querying: who ordered Soda? You'd need a LIKE '%Soda%' search.
-- That can't use an index. On a million-row table, this is a full scan every time.
SELECT customer_name
FROM   orders_broken
WHERE  ordered_items LIKE '%Soda%'; -- Fragile and slow


-- ============================================================
-- AFTER 1NF: Decomposed into atomic rows
-- Every cell holds exactly one value.
-- Primary key: (order_id, item_name) — uniquely identifies each row.
-- ============================================================

CREATE TABLE orders_1nf (
    order_id      INT,
    customer_name VARCHAR(100),
    item_name     VARCHAR(100),   -- ONE item per row
    item_price    DECIMAL(6, 2),  -- ONE price per row
    PRIMARY KEY (order_id, item_name)  -- Composite PK enforces uniqueness
);

INSERT INTO orders_1nf VALUES
    (1, 'Alice Chen',  'Burger', 5.99),
    (1, 'Alice Chen',  'Fries',  2.49),
    (1, 'Alice Chen',  'Soda',   1.99),
    (2, 'Bob Martins', 'Pizza',  8.99),
    (2, 'Bob Martins', 'Soda',   1.99);

-- Now the same query is clean, indexable, and accurate:
SELECT DISTINCT customer_name
FROM   orders_1nf
WHERE  item_name = 'Soda';
-- Uses an index on item_name. Fast at any scale.

-- ============================================================
-- STILL BROKEN (1NF but not 2NF):
-- Primary key: (order_id, item_name)
-- Problem: customer_name depends only on order_id, not on item_name
-- Symptom: If 'Alice Chen' updates her name, we touch N rows instead of 1
-- ============================================================

-- orders_1nf table (from previous step) violates 2NF:
-- order_id | customer_name | item_name | item_price
--    1     | Alice Chen    | Burger    | 5.99   <-- customer_name repeated
--    1     | Alice Chen    | Fries     | 2.49   <-- customer_name repeated
--    1     | Alice Chen    | Soda      | 1.99   <-- customer_name repeated


-- ============================================================
-- AFTER 2NF: Split into three focused tables
-- Each non-key attribute now depends on the FULL primary key of its table.
-- ============================================================

-- Table 1: Customers — customer_name depends only on customer_id
CREATE TABLE customers (
    customer_id   INT          PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL
);

INSERT INTO customers VALUES
    (101, 'Alice Chen'),
    (102, 'Bob Martins');


-- Table 2: Orders — links a customer to a specific order
-- customer_id depends on order_id (not on any item)
CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

INSERT INTO orders VALUES
    (1, 101),
    (2, 102);


-- Table 3: Order Items — the composite PK is (order_id, item_name)
-- item_price depends on BOTH: which order AND which item (price can vary per order)
CREATE TABLE order_items (
    order_id   INT,
    item_name  VARCHAR(100),
    item_price DECIMAL(6, 2) NOT NULL,
    PRIMARY KEY (order_id, item_name),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

INSERT INTO order_items VALUES
    (1, 'Burger', 5.99),
    (1, 'Fries',  2.49),
    (1, 'Soda',   1.99),
    (2, 'Pizza',  8.99),
    (2, 'Soda',   1.99);


-- Now a name update touches exactly ONE row, not N rows:
UPDATE customers
SET    customer_name = 'Alice Zhang'
WHERE  customer_id = 101;
-- 1 row updated. No risk of partial update leaving inconsistent data.


-- Joining back is clean and readable:
SELECT
    c.customer_name,
    oi.item_name,
    oi.item_price
FROM   orders      o
JOIN   customers   c  ON c.customer_id = o.customer_id
JOIN   order_items oi ON oi.order_id   = o.order_id
ORDER BY c.customer_name, oi.item_name;

-- ============================================================
-- BEFORE 3NF: products table has a transitive dependency
-- Primary key: product_id
-- Dependency chain:
--   product_id --> category_name --> category_tax_rate
-- category_tax_rate does NOT directly depend on product_id.
-- It depends on category_name, which depends on product_id.
-- ============================================================

CREATE TABLE products_broken (
    product_id       INT          PRIMARY KEY,
    product_name     VARCHAR(100) NOT NULL,
    category_name    VARCHAR(100) NOT NULL,
    category_tax_rate DECIMAL(5, 4) NOT NULL  -- e.g. 0.0750 = 7.5%
);

INSERT INTO products_broken VALUES
    (1, 'Wireless Mouse',   'Electronics', 0.0750),
    (2, 'Mechanical Keyboard', 'Electronics', 0.0750),  -- tax rate duplicated
    (3, 'Desk Lamp',        'Electronics', 0.0750),     -- duplicated again
    (4, 'Notebook',         'Stationery',  0.0500),
    (5, 'Ballpoint Pen',    'Stationery',  0.0500);     -- duplicated

-- Anomaly demo: Electronics tax rate changes from 7.5% to 8.25%
-- You MUST update every Electronics row or your data becomes inconsistent:
UPDATE products_broken
SET    category_tax_rate = 0.0825
WHERE  category_name = 'Electronics';  -- 3 rows touched. What if one fails mid-transaction?


-- ============================================================
-- AFTER 3NF: Extract the transitive dependency into its own table
-- category_tax_rate now lives in ONE place per category.
-- ============================================================

-- Table 1: Categories — category_tax_rate directly depends on category_id
CREATE TABLE categories (
    category_id   INT          PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL UNIQUE, -- name is still unique
    tax_rate      DECIMAL(5, 4) NOT NULL         -- one source of truth
);

INSERT INTO categories VALUES
    (10, 'Electronics', 0.0750),
    (20, 'Stationery',  0.0500);


-- Table 2: Products — references category by FK, no tax rate duplication
CREATE TABLE products (
    product_id   INT          PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category_id  INT          NOT NULL,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

INSERT INTO products VALUES
    (1, 'Wireless Mouse',      10),
    (2, 'Mechanical Keyboard', 10),
    (3, 'Desk Lamp',           10),
    (4, 'Notebook',            20),
    (5, 'Ballpoint Pen',       20);


-- Now the tax rate update touches exactly ONE row:
UPDATE categories
SET    tax_rate = 0.0825
WHERE  category_name = 'Electronics';
-- 1 row updated. Every Electronics product automatically reflects 8.25%.


-- Verify: all Electronics products now use the updated rate
SELECT
    p.product_name,
    c.category_name,
    c.tax_rate,
    ROUND(p.product_id * 10.00 * c.tax_rate, 2) AS sample_tax  -- illustrative
FROM   products   p
JOIN   categories c ON c.category_id = p.category_id
ORDER BY c.category_name, p.product_name;

-- ============================================================
-- SCENARIO: A product listing page needs to display
-- product name + category name for every product.
-- If this query runs 50,000 times/second, the JOIN might matter.
--
-- Step 1: Measure first. Run EXPLAIN ANALYZE in Postgres:
-- ============================================================

EXPLAIN ANALYZE
SELECT
    p.product_name,
    c.category_name
FROM   products   p
JOIN   categories c ON c.category_id = p.category_id;

-- If the planner uses index scans and costs are low, you're done.
-- Only proceed with denormalization if this is a proven bottleneck.


-- ============================================================
-- SURGICAL DENORMALIZATION: Cache category_name on products
-- Do this ONLY after profiling confirms the JOIN is too slow.
-- Accept the tradeoff: you now own the responsibility of keeping
-- category_name in sync when categories are renamed.
-- ============================================================

ALTER TABLE products
    ADD COLUMN category_name_cache VARCHAR(100); -- Denormalized column, explicitly named _cache

-- Populate the cache from the normalized source of truth:
UPDATE products p
SET    category_name_cache = c.category_name
FROM   categories c
WHERE  c.category_id = p.category_id;

-- You MUST maintain this cache via a trigger or application logic:
CREATE OR REPLACE FUNCTION sync_category_name_cache()
RETURNS TRIGGER AS $$
BEGIN
    -- When a category name changes, propagate it to the cache column
    UPDATE products
    SET    category_name_cache = NEW.category_name
    WHERE  category_id = NEW.category_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_category_name
AFTER UPDATE OF category_name ON categories
FOR EACH ROW
EXECUTE FUNCTION sync_category_name_cache();

-- Now the listing query needs no JOIN:
SELECT product_name, category_name_cache
FROM   products;
-- Faster read. But now you have a maintenance responsibility.
-- This is the tradeoff. Make it consciously, document it clearly.