Database Normalization Explained: 1NF to 3NF With Real Examples

-- ============================================================
-- BEFORE: A contacts table that violates First Normal Form
-- Problem 1: phone_numbers stores multiple values in one cell
-- Problem 2: No clean primary key (name isn't unique enough)
-- ============================================================

CREATE TABLE contacts_unnormalized (
    contact_name    VARCHAR(100),
    email           VARCHAR(150),
    phone_numbers   VARCHAR(255)   -- '555-1234, 555-5678' <-- BAD
);

INSERT INTO contacts_unnormalized VALUES
    ('Maria Garcia',  'maria@example.com',  '555-1234, 555-9999'),
    ('James Okafor',  'james@example.com',  '555-5678'),
    ('Maria Garcia',  'maria2@example.com', '555-0001');  -- duplicate name, now what?

-- ============================================================
-- AFTER: Restructured to satisfy First Normal Form
-- Each table has a clear primary key.
-- Each cell holds exactly one value.
-- Phone numbers get their own table — one row per number.
-- ============================================================

CREATE TABLE contacts (
    contact_id   INT           PRIMARY KEY AUTO_INCREMENT,
    full_name    VARCHAR(100)  NOT NULL,
    email        VARCHAR(150)  NOT NULL UNIQUE
);

CREATE TABLE contact_phone_numbers (
    phone_id     INT           PRIMARY KEY AUTO_INCREMENT,
    contact_id   INT           NOT NULL,
    phone_number VARCHAR(20)   NOT NULL,          -- one number per row
    phone_type   VARCHAR(20)   DEFAULT 'mobile',  -- 'mobile', 'home', 'work'
    FOREIGN KEY (contact_id) REFERENCES contacts(contact_id)
);

-- Insert contacts first (parent table)
INSERT INTO contacts (full_name, email) VALUES
    ('Maria Garcia', 'maria@example.com'),   -- gets contact_id = 1
    ('James Okafor', 'james@example.com');   -- gets contact_id = 2

-- Insert phone numbers (child table) — Maria has two, James has one
INSERT INTO contact_phone_numbers (contact_id, phone_number, phone_type) VALUES
    (1, '555-1234', 'mobile'),   -- Maria's mobile
    (1, '555-9999', 'work'),     -- Maria's work — clean, no comma lists
    (2, '555-5678', 'mobile');   -- James's mobile

-- Now finding all contacts with a specific number is fast and correct
SELECT
    c.full_name,
    p.phone_number,
    p.phone_type
FROM contacts c
JOIN contact_phone_numbers p ON c.contact_id = p.contact_id
ORDER BY c.full_name, p.phone_type;

-- ============================================================
-- BEFORE: order_items violates Second Normal Form
-- Composite PK: (order_id, product_id)
-- PROBLEM: product_name and unit_price depend only on product_id
--          not on the full composite key.
-- This means updating a product name requires touching every order.
-- ============================================================

CREATE TABLE order_items_bad (
    order_id        INT,
    product_id      INT,
    product_name    VARCHAR(100),  -- partial dependency on product_id only
    unit_price      DECIMAL(10,2), -- partial dependency on product_id only
    quantity        INT,           -- this is fine: depends on BOTH order_id + product_id
    PRIMARY KEY (order_id, product_id)
);

INSERT INTO order_items_bad VALUES
    (101, 42, 'Wireless Keyboard', 49.99, 2),
    (102, 42, 'Wireless Keyboard', 49.99, 1),  -- product name duplicated
    (103, 42, 'Wireless Keyboard', 49.99, 3);  -- and again...

-- If the product is renamed to 'BT Keyboard', we must update 3 rows.
-- If we miss one row, we have contradictory product names in the database.
UPDATE order_items_bad
    SET product_name = 'BT Keyboard'
    WHERE product_id = 42;  -- easy to forget a WHERE clause here

-- ============================================================
-- AFTER: Properly separated into 2NF-compliant tables
-- Products table owns product facts.
-- order_items table owns only order-specific facts.
-- ============================================================

CREATE TABLE products (
    product_id    INT            PRIMARY KEY,
    product_name  VARCHAR(100)   NOT NULL,
    unit_price    DECIMAL(10,2)  NOT NULL  -- current price lives here
);

CREATE TABLE orders (
    order_id      INT   PRIMARY KEY AUTO_INCREMENT,
    customer_id   INT   NOT NULL,
    ordered_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
    order_id      INT             NOT NULL,
    product_id    INT             NOT NULL,
    quantity      INT             NOT NULL,
    -- Store price_at_purchase separately — product price can change later
    -- This is intentional: it records the price that was actually charged
    price_at_purchase DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id)   REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

INSERT INTO products VALUES
    (42, 'Wireless Keyboard', 49.99);

-- Now renaming the product only ever touches ONE row in ONE table
UPDATE products
    SET product_name = 'BT Keyboard'
    WHERE product_id = 42;

-- Query still works perfectly — no data inconsistency possible
SELECT
    oi.order_id,
    p.product_name,
    oi.quantity,
    oi.price_at_purchase,
    (oi.quantity * oi.price_at_purchase) AS line_total
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
ORDER BY oi.order_id;

-- ============================================================
-- BEFORE: employees table with a transitive dependency
-- PK: employee_id
-- department_name depends on department_id (not employee_id)
-- department_budget depends on department_id (not employee_id)
-- This is the transitive chain: employee_id -> department_id -> budget
-- ============================================================

CREATE TABLE employees_bad (
    employee_id       INT           PRIMARY KEY,
    employee_name     VARCHAR(100)  NOT NULL,
    department_id     INT           NOT NULL,
    department_name   VARCHAR(100),  -- depends on department_id, not employee_id
    department_budget DECIMAL(12,2)  -- depends on department_id, not employee_id
);

INSERT INTO employees_bad VALUES
    (1, 'Aisha Kamara',   10, 'Engineering', 500000.00),
    (2, 'Leo Petrov',     10, 'Engineering', 500000.00),  -- budget duplicated
    (3, 'Nadia Osei',     20, 'Marketing',   200000.00);

-- Engineering gets a budget increase. We must update every Engineering employee row.
-- Miss one row and the database contradicts itself.
UPDATE employees_bad
    SET department_budget = 600000.00
    WHERE department_id = 10;
-- If this WHERE clause had a bug, one employee would show the old budget forever.

-- ============================================================
-- AFTER: 3NF compliant — transitive dependency eliminated
-- departments table owns department facts.
-- employees table owns only employee facts + a FK to departments.
-- ============================================================

CREATE TABLE departments (
    department_id     INT            PRIMARY KEY,
    department_name   VARCHAR(100)   NOT NULL UNIQUE,
    department_budget DECIMAL(12,2)  NOT NULL
);

CREATE TABLE employees (
    employee_id     INT           PRIMARY KEY,
    employee_name   VARCHAR(100)  NOT NULL,
    department_id   INT           NOT NULL,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

INSERT INTO departments VALUES
    (10, 'Engineering', 500000.00),
    (20, 'Marketing',   200000.00);

INSERT INTO employees VALUES
    (1, 'Aisha Kamara', 10),
    (2, 'Leo Petrov',   10),
    (3, 'Nadia Osei',   20);

-- Budget increase: ONE row updated, zero risk of inconsistency
UPDATE departments
    SET department_budget = 600000.00
    WHERE department_id = 10;

-- Full view of employees with their department details via JOIN
SELECT
    e.employee_id,
    e.employee_name,
    d.department_name,
    d.department_budget
FROM employees e
JOIN departments d ON e.department_id = d.department_id
ORDER BY d.department_name, e.employee_name;

-- ============================================================
-- SCENARIO: An e-commerce order summary page
-- Problem: Recalculating order totals from line items on every
--          page load is destroying database performance.
-- Solution: Store a denormalized order_total on the orders table
--           and keep it in sync via a trigger.
-- ============================================================

-- Normalized base tables (we keep these — they're the source of truth)
CREATE TABLE orders (
    order_id       INT            PRIMARY KEY AUTO_INCREMENT,
    customer_id    INT            NOT NULL,
    ordered_at     TIMESTAMP      DEFAULT CURRENT_TIMESTAMP,
    -- Denormalized cache column — intentionally redundant
    -- Updated automatically by the trigger below
    order_total    DECIMAL(12,2)  DEFAULT 0.00
);

CREATE TABLE order_items (
    item_id            INT            PRIMARY KEY AUTO_INCREMENT,
    order_id           INT            NOT NULL,
    product_id         INT            NOT NULL,
    quantity           INT            NOT NULL,
    price_at_purchase  DECIMAL(10,2)  NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

-- ============================================================
-- Trigger: Automatically recalculates and updates order_total
-- whenever an order_item is inserted, updated, or deleted.
-- This keeps the denormalized column consistent with no manual effort.
-- ============================================================

DELIMITER //
CREATE TRIGGER sync_order_total
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    -- Recalculate and store the total for the affected order
    UPDATE orders
    SET order_total = (
        SELECT COALESCE(SUM(quantity * price_at_purchase), 0)
        FROM order_items
        WHERE order_id = NEW.order_id  -- only recalculate the affected order
    )
    WHERE order_id = NEW.order_id;
END//
DELIMITER ;

-- Insert an order and its items
INSERT INTO orders (customer_id) VALUES (7);

INSERT INTO order_items (order_id, product_id, quantity, price_at_purchase) VALUES
    (1, 42, 2, 49.99),   -- 2x keyboards
    (1, 88, 1, 29.99);   -- 1x mouse

-- The order summary page now reads ONE row from orders — no join needed
-- This is the read that happens thousands of times per second
SELECT
    order_id,
    customer_id,
    order_total,  -- already computed, instant read
    ordered_at
FROM orders
WHERE order_id = 1;

-- The normalized detail view still works perfectly when you need it
SELECT
    oi.order_id,
    oi.product_id,
    oi.quantity,
    oi.price_at_purchase,
    (oi.quantity * oi.price_at_purchase) AS line_total
FROM order_items oi
WHERE oi.order_id = 1;