Data Modeling Patterns & Best Practices

Practical modeling techniques for real-world applications

Beyond Normalization

While lesson 7 covered normalization theory, real-world database modeling requires practical patterns that handle time, change tracking, and performance trade-offs. This lesson explores production-tested techniques used by companies like Stripe, Airbnb, and Netflix.

Real-World Impact:

Stripe: Uses temporal modeling to track subscription price changes over time
Airbnb: Implements soft deletes to preserve booking history and regulatory data
Netflix: Denormalizes viewing data for sub-100ms recommendation queries

ER Diagrams in Practice

Entity-Relationship diagrams translate business requirements into database structure. Let's model a practical e-commerce system with orders, customers, and inventory.

Step 1: Identify Entities and Relationships

# Business Requirements:
# - Customers place orders containing multiple products
# - Products belong to categories
# - Orders have status and payment information
# - Track inventory levels per product

Entities:
  Customer (name, email, address)
  Order (order_date, status, total_amount)
  Product (name, price, stock_quantity)
  Category (name, description)

Relationships:
  Customer --< Order      (one-to-many: customer has many orders)
  Order >--< Product      (many-to-many: orders contain many products)
  Product >-- Category    (many-to-one: product belongs to category)

Result: We've identified 4 entities and 3 relationships. The many-to-many between Order and Product requires a junction table (OrderItem).

Step 2: Translate to Database Schema

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    address TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT
);

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    category_id INTEGER REFERENCES categories(category_id),
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'pending',
    total_amount DECIMAL(10, 2) NOT NULL
);

-- Junction table for many-to-many relationship
CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(order_id),
    product_id INTEGER REFERENCES products(product_id),
    quantity INTEGER NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,  -- Capture price at time of order
    UNIQUE(order_id, product_id)
);

Result: The ER diagram is now a working schema. Note that order_items.unit_pricecaptures the price at purchase time, essential for historical accuracy if product prices change.

Step 3: Query the Model

import psycopg2

conn = psycopg2.connect("dbname=ecommerce user=postgres")
cur = conn.cursor()

# Get customer's order history with product details
query = """
    SELECT
        o.order_id,
        o.order_date,
        o.status,
        p.name AS product_name,
        oi.quantity,
        oi.unit_price,
        (oi.quantity * oi.unit_price) AS line_total
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN products p ON oi.product_id = p.product_id
    WHERE o.customer_id = %s
    ORDER BY o.order_date DESC
"""

cur.execute(query, (customer_id,))
orders = cur.fetchall()

Result: Returns complete order history with product details. The junction table enables efficient querying across the many-to-many relationship.

Temporal Data Modeling

Temporal modeling tracks how data changes over time. Essential for subscriptions, pricing history, employee records, and regulatory compliance.

Pattern: Effective Dating

Without Temporal Modeling

CREATE TABLE prices (
    product_id INT PRIMARY KEY,
    price DECIMAL(10, 2)
);

-- Problem: Overwrites history!
UPDATE prices
SET price = 29.99
WHERE product_id = 1;

-- Lost: What was the old price?
-- Lost: When did it change?

With Temporal Modeling

CREATE TABLE price_history (
    product_id INT,
    price DECIMAL(10, 2),
    effective_from TIMESTAMP,
    effective_to TIMESTAMP,
    PRIMARY KEY (product_id, effective_from)
);

-- Preserves complete history!
-- Can query: "What was price on 2024-03-15?"
-- Can track: "How often do prices change?"

Implementation: Subscription Price Changes

CREATE TABLE subscription_prices (
    subscription_id INTEGER,
    price DECIMAL(10, 2) NOT NULL,
    effective_from TIMESTAMP NOT NULL,
    effective_to TIMESTAMP DEFAULT '9999-12-31',  -- "infinity" for current
    change_reason VARCHAR(100),
    PRIMARY KEY (subscription_id, effective_from)
);

-- Index for "current price" queries
CREATE INDEX idx_current_prices
ON subscription_prices (subscription_id, effective_to)
WHERE effective_to = '9999-12-31';

Result: Complete price history. The index on effective_to makes "get current price" queries fast despite historical data.

Querying Temporal Data

def get_current_price(conn, subscription_id):
    """Get the active price for a subscription."""
    cur = conn.cursor()
    cur.execute("""
        SELECT price
        FROM subscription_prices
        WHERE subscription_id = %s
          AND effective_to = '9999-12-31'
    """, (subscription_id,))
    return cur.fetchone()[0]

def get_price_at_date(conn, subscription_id, target_date):
    """Get the price that was effective on a specific date."""
    cur = conn.cursor()
    cur.execute("""
        SELECT price
        FROM subscription_prices
        WHERE subscription_id = %s
          AND effective_from <= %s
          AND effective_to > %s
        ORDER BY effective_from DESC
        LIMIT 1
    """, (subscription_id, target_date, target_date))
    return cur.fetchone()[0]

Result: Two query patterns cover 95% of temporal needs: "current value" and "value at specific time."

Adding New Price Periods

def change_subscription_price(conn, subscription_id, new_price, effective_date, reason):
    """Change subscription price while preserving history.
    Both steps run in a single transaction (psycopg2 default).
    """
    cur = conn.cursor()

    try:
        # Step 1: Close the current price period
        cur.execute("""
            UPDATE subscription_prices
            SET effective_to = %s
            WHERE subscription_id = %s
              AND effective_to = '9999-12-31'
        """, (effective_date, subscription_id))

        # Step 2: Insert new price period
        cur.execute("""
            INSERT INTO subscription_prices
                (subscription_id, price, effective_from, effective_to, change_reason)
            VALUES (%s, %s, %s, '9999-12-31', %s)
        """, (subscription_id, new_price, effective_date, reason))

        conn.commit()  # Both steps succeed → commit transaction
    except Exception:
        conn.rollback()  # Either step fails → undo both
        raise

Result: Price history is immutable. Old periods are closed (never deleted), new periods are added. This pattern enables audit trails and regulatory compliance.

Audit Tables & Change Tracking

Audit tables record who changed what and when. Critical for compliance (SOX, HIPAA), security investigations, and debugging production issues.

Pattern 1: Shadow Audit Table

-- Original table
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL,
    role VARCHAR(20) NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Audit table: mirrors structure + audit columns
CREATE TABLE users_audit (
    audit_id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    username VARCHAR(50),
    email VARCHAR(255),
    role VARCHAR(20),
    -- Audit metadata
    change_type VARCHAR(10) NOT NULL,  -- 'INSERT', 'UPDATE', 'DELETE'
    changed_by VARCHAR(100) NOT NULL,  -- User who made the change
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    old_values JSONB,  -- Store complete before-state for UPDATEs
    new_values JSONB   -- Store complete after-state
);

CREATE INDEX idx_users_audit_user ON users_audit(user_id, changed_at);

Result: Every change to users is recorded in users_audit. The JSONB columns store complete before/after snapshots for complex analysis.

Pattern 2: Automatic Audit with Triggers

CREATE OR REPLACE FUNCTION audit_users_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'DELETE') THEN
        INSERT INTO users_audit (
            user_id, username, email, role,
            change_type, changed_by, old_values
        ) VALUES (
            OLD.user_id, OLD.username, OLD.email, OLD.role,
            'DELETE', current_user, row_to_json(OLD)
        );
        RETURN OLD;

    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO users_audit (
            user_id, username, email, role,
            change_type, changed_by, old_values, new_values
        ) VALUES (
            NEW.user_id, NEW.username, NEW.email, NEW.role,
            'UPDATE', current_user, row_to_json(OLD), row_to_json(NEW)
        );
        RETURN NEW;

    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO users_audit (
            user_id, username, email, role,
            change_type, changed_by, new_values
        ) VALUES (
            NEW.user_id, NEW.username, NEW.email, NEW.role,
            'INSERT', current_user, row_to_json(NEW)
        );
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Attach trigger
CREATE TRIGGER users_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION audit_users_changes();

Result: Every INSERT, UPDATE, DELETE on users automatically creates an audit record. No application code required, the database handles it.

Querying Audit History

def get_user_change_history(conn, user_id):
    """Retrieve complete audit trail for a user."""
    cur = conn.cursor()
    cur.execute("""
        SELECT
            changed_at,
            change_type,
            changed_by,
            old_values->>'email' AS old_email,
            new_values->>'email' AS new_email,
            old_values->>'role' AS old_role,
            new_values->>'role' AS new_role
        FROM users_audit
        WHERE user_id = %s
        ORDER BY changed_at DESC
    """, (user_id,))

    return cur.fetchall()

# Example result:
# 2024-12-01 15:23:45 | UPDATE | admin@company.com | john@old.com | john@new.com | user | admin
# 2024-11-15 10:12:30 | INSERT | system | None | john@old.com | None | user

Result: Complete change history: who changed what field from which value to which value, and when. Essential for compliance audits and security investigations.

Soft Deletes vs Hard Deletes

How you handle deletions impacts data recovery, compliance, and referential integrity. Choose the right pattern for each table.

Soft Delete

Mark records as deleted without removing them from the database.

When to use:

User accounts (preserve audit trail of account changes)
Financial records (regulatory compliance)
Parent records with children (preserve referential integrity)
Undo functionality needed

Pros:

Can restore deleted records
Preserves audit trail
No cascade deletion issues

Cons:

Tables grow indefinitely
Must filter deleted records in all queries
Indexes include deleted data

Hard Delete

Physically remove records from the database.

When to use:

Temporary data (sessions, tokens)
Truly anonymize data (GDPR "right to be forgotten")
Performance-critical tables
No restoration needed

Pros:

Tables stay lean
Simpler queries (no deletion filter)
Better performance

Cons:

Irreversible (unless backed up)
No audit trail
Cascade deletions can be dangerous

Implementation: Soft Delete

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL,
    deleted_at TIMESTAMP NULL,  -- NULL = active, timestamp = deleted
    deleted_by VARCHAR(100) NULL
);

-- Index for filtering out deleted records
CREATE INDEX idx_users_active ON users(user_id) WHERE deleted_at IS NULL;

-- Unique constraint only for active records
CREATE UNIQUE INDEX idx_users_email_active
ON users(email)
WHERE deleted_at IS NULL;

Result: deleted_at IS NULL means active record. Partial index makes queries on active records fast. Unique constraint only applies to active records, allows multiple deleted users with same email.

Application Code Patterns

def soft_delete_user(conn, user_id, deleted_by):
    """Soft delete a user."""
    cur = conn.cursor()
    cur.execute("""
        UPDATE users
        SET deleted_at = CURRENT_TIMESTAMP,
            deleted_by = %s
        WHERE user_id = %s
          AND deleted_at IS NULL
    """, (deleted_by, user_id))
    conn.commit()
    return cur.rowcount > 0  # False if already deleted

def get_active_users(conn):
    """Get all active (non-deleted) users."""
    cur = conn.cursor()
    cur.execute("""
        SELECT user_id, username, email
        FROM users
        WHERE deleted_at IS NULL
        ORDER BY username
    """)
    return cur.fetchall()

Result: Every query must filter WHERE deleted_at IS NULL. Consider creating a view to avoid repeating this logic.

View Pattern for Simplicity

-- Create view of active records
CREATE VIEW users_active AS
SELECT user_id, username, email, created_at
FROM users
WHERE deleted_at IS NULL;

-- Application code uses the view (simpler!)
def get_active_users_simple(conn):
    cur = conn.cursor()
    cur.execute("SELECT * FROM users_active ORDER BY username")
    return cur.fetchall()

# Result: Application code doesn't need to remember the deleted_at filter

Result: Views abstract the soft delete logic. Application code queriesusers_active view instead of filtering manually, cleaner and less error-prone.

Denormalization Patterns

Lesson 7 taught normalization to eliminate redundancy. But sometimes you intentionally denormalize for performance. The key is knowing when and how.

When to Denormalize

High-Read, Low-Write

Data read frequently but updated rarely.

Example:
Product category name
copied to products table

Reads: 1M/day
Writes: 10/day

Trade-off accepted ✓

Expensive Joins

Query requires 4+ table joins and is slow.

Example:
Materialize user's
total_orders_count

Before: 5-table join, 800ms
After: Single column, 2ms

Trade-off accepted ✓

Aggregations

SUM, COUNT, AVG computed repeatedly.

Example:
Store order.total_amount
instead of SUM(items.price)

Before: Recalc on every view
After: Read cached value

Trade-off accepted ✓

Pattern 1: Caching Aggregations

-- Normalized (slow for large orders)
SELECT o.order_id, SUM(oi.quantity * oi.unit_price) AS total
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_id = 12345
GROUP BY o.order_id;

-- Denormalized (fast, pre-computed)
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    order_date TIMESTAMP,
    total_amount DECIMAL(10, 2) NOT NULL,  -- Cached aggregate
    item_count INTEGER NOT NULL             -- Cached count
);

SELECT order_id, total_amount FROM orders WHERE order_id = 12345;
-- Result: 100x faster for orders with many items

Result: Reading order total is instant, no join or aggregation needed. Trade-off: must keep total_amount in sync when items are added/removed.

Maintaining Consistency with Triggers

CREATE OR REPLACE FUNCTION update_order_totals()
RETURNS TRIGGER AS $$
BEGIN
    -- Recalculate order totals when order_items change
    UPDATE orders
    SET
        total_amount = (
            SELECT COALESCE(SUM(quantity * unit_price), 0)
            FROM order_items
            WHERE order_id = COALESCE(NEW.order_id, OLD.order_id)
        ),
        item_count = (
            SELECT COALESCE(COUNT(*), 0)
            FROM order_items
            WHERE order_id = COALESCE(NEW.order_id, OLD.order_id)
        )
    WHERE order_id = COALESCE(NEW.order_id, OLD.order_id);

    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER order_items_changes
AFTER INSERT OR UPDATE OR DELETE ON order_items
FOR EACH ROW EXECUTE FUNCTION update_order_totals();

-- Test the trigger
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (12345, 101, 2, 19.99);

SELECT total_amount, item_count FROM orders WHERE order_id = 12345;
-- Result: total_amount automatically updated to 39.98, item_count = 1

Result: Trigger automatically maintains consistency. When an order item is inserted/updated/deleted, the order's total_amount and item_countare recalculated. Application code doesn't need to manage this.

Pattern 2: Copying Foreign Key Names

-- Normalized (requires join)
SELECT p.product_id, p.name, c.name AS category_name
FROM products p
JOIN categories c ON p.category_id = c.category_id
WHERE p.product_id = 101;

-- Denormalized (no join needed)
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    category_id INTEGER REFERENCES categories(category_id),
    category_name VARCHAR(100) NOT NULL,  -- Denormalized copy!
    price DECIMAL(10, 2)
);

SELECT product_id, name, category_name FROM products WHERE product_id = 101;
-- Result: 10x faster, no join required

Result: category_name is duplicated from the categoriestable. Read queries are faster, but updates to category names must propagate to all products.

Pattern 3: Materialized Views

-- Expensive query: user statistics (5-table join, 2-second execution)
CREATE MATERIALIZED VIEW user_stats AS
SELECT
    u.user_id,
    u.username,
    COUNT(DISTINCT o.order_id) AS total_orders,
    COALESCE(SUM(o.total_amount), 0) AS lifetime_value,
    MAX(o.order_date) AS last_order_date,
    COUNT(DISTINCT r.review_id) AS review_count,
    AVG(r.rating) AS avg_rating
FROM users u
LEFT JOIN orders o ON u.user_id = o.customer_id
LEFT JOIN reviews r ON u.user_id = r.user_id
GROUP BY u.user_id, u.username;

CREATE INDEX idx_user_stats_user ON user_stats(user_id);

-- Query the materialized view (instant results!)
SELECT * FROM user_stats WHERE user_id = 42;
-- Result: <5ms instead of 2000ms

Result: Materialized view stores query results as a physical table. Perfect for expensive analytics queries. Must refresh periodically to stay current.

Refreshing Materialized Views

-- Manual refresh (locks the view)
REFRESH MATERIALIZED VIEW user_stats;

-- Concurrent refresh (doesn't lock, requires unique index)
CREATE UNIQUE INDEX idx_user_stats_user_unique ON user_stats(user_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats;

# Python: Schedule periodic refreshes
import schedule
import psycopg2

def refresh_user_stats():
    conn = psycopg2.connect("dbname=myapp")
    cur = conn.cursor()
    cur.execute("REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats")
    conn.commit()
    print(f"Refreshed user_stats at {datetime.now()}")

schedule.every(1).hours.do(refresh_user_stats)  # Refresh hourly

Result: CONCURRENTLY allows reads during refresh, users see slightly stale data but no downtime. Hourly refresh means data is at most 1 hour behind reality.

Modeling Best Practices Checklist

Do This

Use temporal modeling for prices, subscriptions, employee records
Add audit tables for sensitive data (users, permissions, financial)
Soft delete user accounts, orders, anything with compliance requirements
Denormalize high-read aggregations, expensive joins, analytics
Use triggers to maintain denormalized data automatically
Create partial indexes on deleted_at IS NULL for soft deletes
Store metadata in JSONB columns for flexible audit trails
Use views to abstract soft delete filtering from application code

Avoid This

Don't hard delete data with regulatory or audit requirements
Don't denormalize frequently-updated data (causes consistency issues)
Don't skip audit logs on permission changes or financial operations
Don't forget effective_to in temporal tables (makes queries complex)
Don't use soft deletes everywhere (temporary data like sessions don't need it)
Don't manually maintain denormalized values (use triggers/views)
Don't forget to refresh materialized views (they become stale)
Don't expose audit tables directly to application users

Modeling Decision Tree

Use this flowchart to decide which patterns to apply when modeling a new table.

1. Does this data change over time and you need history?

YES → Use temporal modeling (effective_from/effective_to)
NO → Continue to question 2

2. Do you need to track WHO changed WHAT and WHEN?

YES → Add audit table with triggers
NO → Continue to question 3

3. When users delete records, do you need to restore them?

YES → Use soft deletes (deleted_at column)
NO, compliance/audit required → Still use soft deletes
NO, truly temporary data → Use hard deletes

4. Is there a slow query (>500ms) with expensive joins or aggregations?

YES, aggregations → Cache aggregates in parent table with triggers
YES, analytics query → Create materialized view
YES, FK name lookups → Denormalize FK names
NO → Keep normalized, you're done!

Pro Tip: Start normalized. Only denormalize when you have evidence (profiling, slow query logs) that it's needed. Premature denormalization causes more problems than it solves.

Back to "Database Migrations"

Databases AdvancedLesson 24

Next: "ORMs & Access Patterns"