sql-data-modeling

SQL Data Modeling

Essential patterns for designing robust, performant database schemas.

Core Principles

1. Model the business domain first - Tables should reflect real-world entities and relationships
2. Normalize for integrity, denormalize for performance - Start normalized, denormalize with evidence
3. Enforce constraints at the database level - Never rely solely on application logic for data integrity
4. Design for query patterns - Schema should serve the most common read and write workloads
5. Plan for change - Use surrogate keys and flexible structures that accommodate evolving requirements

Normalization Fundamentals

-- Unnormalized: repeating groups and mixed concerns
-- orders(order_id, customer_name, customer_email, item1, price1, item2, price2)

-- 1NF: Atomic values, no repeating groups
CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    customer_id  INT NOT NULL,
    order_date   DATE NOT NULL
);

CREATE TABLE order_items (
    order_item_id  INT PRIMARY KEY,
    order_id       INT NOT NULL REFERENCES orders(order_id),
    product_name   VARCHAR(200) NOT NULL,
    unit_price     NUMERIC(10,2) NOT NULL,
    quantity       INT NOT NULL CHECK (quantity > 0)
);

-- 2NF: Remove partial dependencies (every non-key depends on full PK)
-- 3NF: Remove transitive dependencies (non-key columns depend only on the PK)
CREATE TABLE customers (
    customer_id  INT PRIMARY KEY,
    name         VARCHAR(100) NOT NULL,
    email        VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE products (
    product_id   INT PRIMARY KEY,
    product_name VARCHAR(200) NOT NULL,
    unit_price   NUMERIC(10,2) NOT NULL
);

See normalization-patterns.md for:

• Step-by-step 1NF through BCNF walkthrough
• When to stop normalizing
• Normal form trade-offs and decision criteria

Denormalization Strategies

-- Precomputed summary table for dashboard queries
CREATE TABLE daily_sales_summary (
    summary_date    DATE NOT NULL,
    product_id      INT NOT NULL REFERENCES products(product_id),
    category_id     INT NOT NULL REFERENCES categories(category_id),
    total_quantity  INT NOT NULL DEFAULT 0,
    total_revenue   NUMERIC(12,2) NOT NULL DEFAULT 0,
    order_count     INT NOT NULL DEFAULT 0,
    PRIMARY KEY (summary_date, product_id)
);

-- Refresh pattern: truncate and reload daily
INSERT INTO daily_sales_summary (summary_date, product_id, category_id, total_quantity, total_revenue, order_count)
SELECT
    o.order_date,
    oi.product_id,
    p.category_id,
    SUM(oi.quantity),
    SUM(oi.quantity * oi.unit_price),
    COUNT(DISTINCT o.order_id)
FROM orders o
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date = CURRENT_DATE - INTERVAL '1 day'
GROUP BY o.order_date, oi.product_id, p.category_id;