1NF, 2NF, 3NF — Transitive Dependencies That Break Billing

-- ============================================================
-- BEFORE 1NF: The multi-value column anti-pattern
-- Problems:
--   1. items and prices are parallel lists — positional coupling
--   2. No index can speed up 'find orders containing Mushroom'
--   3. Price of Mushroom is only knowable by counting commas
--   4. Adding a third item requires application-layer parsing
-- ============================================================
CREATE TABLE io_thecodeforge.raw_orders (
    order_id      INT,
    customer_name VARCHAR(100),
    items         TEXT,   -- 'Pepperoni, Mushroom, Olives'
    prices        TEXT    -- '12.00, 2.00, 1.50'
);

INSERT INTO io_thecodeforge.raw_orders VALUES
    (1, 'Alice Chen',    'Pepperoni, Mushroom', '12.00, 2.00'),
    (2, 'Bob Okafor',    'Veggie, Olives',      '10.00, 1.50'),
    (3, 'Alice Chen',    'Mushroom',             '2.00');

-- This query requires a sequential scan on every row.
-- No index helps. Runtime degrades linearly with table size.
SELECT order_id, customer_name
FROM io_thecodeforge.raw_orders
WHERE items LIKE '%Mushroom%';

-- ============================================================
-- AFTER 1NF: One atomic value per column, one fact per row
-- Composite PK (order_id, item_name) enforces uniqueness
-- and gives the optimizer a B-tree index to work with
-- ============================================================
CREATE TABLE io_thecodeforge.orders_1nf (
    order_id   INT,
    item_name  VARCHAR(100),
    item_price DECIMAL(10, 2)  NOT NULL CHECK (item_price >= 0),
    PRIMARY KEY (order_id, item_name)
);

-- customer_name still lives here for now — we fix that in 2NF
CREATE TABLE io_thecodeforge.order_headers_1nf (
    order_id      INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL
);

INSERT INTO io_thecodeforge.order_headers_1nf VALUES
    (1, 'Alice Chen'),
    (2, 'Bob Okafor'),
    (3, 'Alice Chen');

INSERT INTO io_thecodeforge.orders_1nf VALUES
    (1, 'Pepperoni', 12.00),
    (1, 'Mushroom',   2.00),
    (2, 'Veggie',    10.00),
    (2, 'Olives',     1.50),
    (3, 'Mushroom',   2.00);

-- Same query now uses the primary key index.
-- Execution plan: Index Scan on orders_1nf
-- Runtime is O(log N) instead of O(N)
SELECT oh.customer_name, oi.order_id
FROM io_thecodeforge.orders_1nf oi
JOIN io_thecodeforge.order_headers_1nf oh
  ON oi.order_id = oh.order_id
WHERE oi.item_name = 'Mushroom';

-- Confirm the query plan uses the index
EXPLAIN (ANALYZE, BUFFERS)
SELECT oh.customer_name, oi.order_id
FROM io_thecodeforge.orders_1nf oi
JOIN io_thecodeforge.order_headers_1nf oh
  ON oi.order_id = oh.order_id
WHERE oi.item_name = 'Mushroom';

-- ============================================================
-- BEFORE 2NF: customer_name in the order_items table
-- Partial dependency: customer_name depends on order_id alone,
-- not on the full composite key (order_id, item_name)
-- ============================================================
-- If Alice changes her name, you must update every row
-- for every item she ever ordered. Miss one: split-brain data.
CREATE TABLE io_thecodeforge.orders_partial_dep (
    order_id      INT,
    customer_name VARCHAR(100),  -- depends only on order_id
    item_name     VARCHAR(100),
    item_price    DECIMAL(10, 2),
    PRIMARY KEY (order_id, item_name)
);

-- This UPDATE must touch every row for Alice's orders
-- It will acquire row-level locks on all of them simultaneously
UPDATE io_thecodeforge.orders_partial_dep
SET customer_name = 'Alice Zhang'
WHERE customer_name = 'Alice Chen';
-- Miss one row: you now have two truths. Neither is reliable.

-- ============================================================
-- AFTER 2NF: Three tables, each with a single responsibility
-- Every non-key column depends on the full key of its table
-- ============================================================

-- Table 1: customers — the single authoritative source for customer identity
CREATE TABLE io_thecodeforge.customers (
    customer_id   SERIAL       PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email         VARCHAR(255) NOT NULL,
    CONSTRAINT uq_customer_email UNIQUE (email)
);

-- Table 2: orders — facts about the order itself (who placed it, when)
CREATE TABLE io_thecodeforge.orders (
    order_id    INT          PRIMARY KEY,
    customer_id INT          NOT NULL
                             REFERENCES io_thecodeforge.customers(customer_id)
                             ON DELETE RESTRICT,
    order_date  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
-- Index the FK — Postgres does NOT do this automatically
CREATE INDEX idx_orders_customer_id
    ON io_thecodeforge.orders(customer_id);

-- Table 3: order_items — facts that genuinely depend on both order AND item
-- item_price here is an intentional snapshot: the price at time of purchase
-- This is NOT a 3NF violation — it is a temporal fact about the order line
CREATE TABLE io_thecodeforge.order_items (
    order_id    INT            NOT NULL
                               REFERENCES io_thecodeforge.orders(order_id)
                               ON DELETE CASCADE,
    item_name   VARCHAR(100)   NOT NULL,
    item_price  DECIMAL(10, 2) NOT NULL CHECK (item_price >= 0),
    quantity    INT            NOT NULL DEFAULT 1 CHECK (quantity > 0),
    PRIMARY KEY (order_id, item_name)
);

-- Alice changes her name: exactly ONE row updated, ONE place, no missed rows
UPDATE io_thecodeforge.customers
SET customer_name = 'Alice Zhang'
WHERE customer_id = 1;
-- Every JOIN to customers.customer_name now returns 'Alice Zhang' automatically

-- Verify referential integrity with a JOIN
SELECT
    c.customer_name,
    o.order_id,
    o.order_date,
    oi.item_name,
    oi.item_price,
    oi.quantity
FROM io_thecodeforge.customers c
JOIN io_thecodeforge.orders o
  ON c.customer_id = o.customer_id
JOIN io_thecodeforge.order_items oi
  ON o.order_id = oi.order_id
ORDER BY o.order_id, oi.item_name;

-- ============================================================
-- BEFORE 3NF: tax_rate stored directly in products
-- Transitive dependency chain:
--   product_id -> category_name -> tax_rate
-- tax_rate depends on category_name, not on product_id
-- ============================================================
CREATE TABLE io_thecodeforge.products_3nf_violation (
    product_id    INT           PRIMARY KEY,
    product_name  VARCHAR(100)  NOT NULL,
    category_name VARCHAR(50)   NOT NULL,
    tax_rate      DECIMAL(5, 4) NOT NULL  -- depends on category, not product
);

-- Government raises Electronics tax rate: 50,000 rows updated
-- Lock held on the entire update: writes queue up behind it
-- Read replicas lag during replication of 50,000 row changes
-- During the update, some rows have 0.08 and some have 0.10
UPDATE io_thecodeforge.products_3nf_violation
SET tax_rate = 0.10
WHERE category_name = 'Electronics';
-- Missed even one row? Billing computes wrong tax until someone notices.

-- ============================================================
-- AFTER 3NF: tax_rate lives in categories where it belongs
-- ============================================================

-- categories: the single authoritative source for category tax rates
-- effective_date enables temporal lookups — critical for billing audits
CREATE TABLE io_thecodeforge.categories (
    category_id   INT           PRIMARY KEY,
    name          VARCHAR(50)   NOT NULL,
    CONSTRAINT uq_category_name UNIQUE (name)
);

-- Tax rates are temporal facts — they change over time
-- Storing them with an effective_date lets you reconstruct
-- the correct rate for any historical transaction
CREATE TABLE io_thecodeforge.category_tax_rates (
    category_id    INT           NOT NULL
                                 REFERENCES io_thecodeforge.categories(category_id),
    effective_date DATE          NOT NULL,
    tax_rate       DECIMAL(5, 4) NOT NULL CHECK (tax_rate >= 0 AND tax_rate <= 1),
    PRIMARY KEY (category_id, effective_date)
);

-- products: no tax_rate column — it is derived at query time
CREATE TABLE io_thecodeforge.products (
    product_id   INT          PRIMARY KEY,
    name         VARCHAR(100) NOT NULL,
    category_id  INT          NOT NULL
                              REFERENCES io_thecodeforge.categories(category_id)
);
CREATE INDEX idx_products_category_id
    ON io_thecodeforge.products(category_id);

-- Government raises Electronics rate: exactly ONE row inserted
-- No existing rows touched, no lock contention, no replication lag
-- Historical rates remain intact for audit queries
INSERT INTO io_thecodeforge.category_tax_rates
    (category_id, effective_date, tax_rate)
VALUES
    (1, '2026-04-01', 0.10);
-- That is the entire rate change. One insert. Done.

-- Billing query: get the correct tax rate for a transaction date
-- The LATERAL subquery finds the most recent rate on or before
-- the invoice date — exactly what was in effect at transaction time
SELECT
    p.name                            AS product_name,
    c.name                            AS category,
    tr.tax_rate,
    tr.effective_date                 AS rate_in_effect_since
FROM io_thecodeforge.products p
JOIN io_thecodeforge.categories c
  ON p.category_id = c.category_id
JOIN LATERAL (
    SELECT tax_rate, effective_date
    FROM io_thecodeforge.category_tax_rates
    WHERE category_id = p.category_id
      AND effective_date <= '2026-03-15'  -- the invoice date
    ORDER BY effective_date DESC
    LIMIT 1
) tr ON true
WHERE p.product_id = 42;

-- Verify 3NF compliance: no product knows its own tax rate
-- The rate is computed at query time from the authoritative lookup
EXPLAIN (ANALYZE, BUFFERS)
SELECT p.name, c.name, tr.tax_rate
FROM io_thecodeforge.products p
JOIN io_thecodeforge.categories c ON p.category_id = c.category_id
JOIN LATERAL (
    SELECT tax_rate FROM io_thecodeforge.category_tax_rates
    WHERE category_id = p.category_id
      AND effective_date <= CURRENT_DATE
    ORDER BY effective_date DESC LIMIT 1
) tr ON true;

-- io.thecodeforge
-- Trap: Normalized schema still allows orphan invoices
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE invoices (
    invoice_id INT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- BUG: No constraint prevents inserting invoice for nonexistent customer
INSERT INTO invoices VALUES (1, 999, 1500.00);  -- Orphan row, report emits phantom revenue

// io.thecodeforge
// Over-normalization leads to slow joins
import java.sql.*;

public class ReportService {
    public void generateCustomerReport(int customerId) {
        String query = """
            SELECT c.name, a.street, o.order_date, p.product_name
            FROM customers c
            JOIN addresses a ON c.customer_id = a.customer_id
            JOIN orders o ON c.customer_id = o.customer_id
            JOIN order_items oi ON o.order_id = oi.order_id
            JOIN products p ON oi.product_id = p.product_id
            WHERE c.customer_id = ?
            """;  // 5 JOINs, production query took 12s
        // Real fix: denormalize into a read model or materialized view
    }
}

-- Original table (3NF but not BCNF)
CREATE TABLE BillingAssignments (
    InvoiceID INT PRIMARY KEY,
    CustomerID INT NOT NULL,
    BillingMethod VARCHAR(50) NOT NULL,
    BillingContact VARCHAR(100) NOT NULL,
    UNIQUE (CustomerID, BillingMethod)
);

-- Decomposed into BCNF
CREATE TABLE BillingContactMethods (
    BillingContact VARCHAR(100) PRIMARY KEY,
    BillingMethod VARCHAR(50) NOT NULL
);

CREATE TABLE BillingAssignments_BCNF (
    InvoiceID INT PRIMARY KEY,
    CustomerID INT NOT NULL,
    BillingContact VARCHAR(100) NOT NULL,
    FOREIGN KEY (BillingContact) REFERENCES BillingContactMethods(BillingContact)
);

-- 1NF Violation: Multi-valued attribute
CREATE TABLE Invoices (
    InvoiceID INT PRIMARY KEY,
    CustomerID INT,
    LineItems VARCHAR(500) -- violation
);

-- Refactored to 1NF
CREATE TABLE InvoiceLineItems (
    InvoiceID INT,
    LineItemID INT,
    Amount DECIMAL(10,2),
    PRIMARY KEY (InvoiceID, LineItemID)
);

-- 2NF Violation: Partial dependency
CREATE TABLE OrderDetails (
    OrderID INT,
    ProductID INT,
    ProductName VARCHAR(100), -- violation
    Quantity INT,
    PRIMARY KEY (OrderID, ProductID)
);

-- Refactored to 2NF
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100)
);
CREATE TABLE OrderDetails_2NF (
    OrderID INT,
    ProductID INT,
    Quantity INT,
    PRIMARY KEY (OrderID, ProductID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

-- 3NF Violation: Transitive dependency
CREATE TABLE Billing (
    InvoiceID INT PRIMARY KEY,
    CustomerID INT,
    CustomerAddress VARCHAR(200) -- violation
);

-- Refactored to 3NF
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerAddress VARCHAR(200)
);
CREATE TABLE Billing_3NF (
    InvoiceID INT PRIMARY KEY,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- Domain-Key Normal Form example with CHECK constraint
CREATE TABLE Invoices (
    InvoiceID INT PRIMARY KEY,
    CustomerID INT NOT NULL,
    Total DECIMAL(10,2) CHECK (Total >= 0),
    IssueDate DATE NOT NULL,
    DueDate DATE NOT NULL,
    CHECK (DueDate >= IssueDate),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- 4NF decomposition for independent multivalued dependencies
CREATE TABLE EmployeeSkills (
    EmployeeID INT,
    Skill VARCHAR(50),
    PRIMARY KEY (EmployeeID, Skill)
);

CREATE TABLE EmployeeLanguages (
    EmployeeID INT,
    Language VARCHAR(50),
    PRIMARY KEY (EmployeeID, Language)
);

-- 5NF example: a join dependency that requires decomposition
-- Original table (not in 5NF)
CREATE TABLE SupplierPartsProjects (
    SupplierID INT,
    PartID INT,
    ProjectID INT,
    PRIMARY KEY (SupplierID, PartID, ProjectID)
);
-- Decomposed into 5NF
CREATE TABLE SupplierParts (
    SupplierID INT,
    PartID INT,
    PRIMARY KEY (SupplierID, PartID)
);
CREATE TABLE SupplierProjects (
    SupplierID INT,
    ProjectID INT,
    PRIMARY KEY (SupplierID, ProjectID)
);
CREATE TABLE PartProjects (
    PartID INT,
    ProjectID INT,
    PRIMARY KEY (PartID, ProjectID)
);