Data Modeling Patterns & Best Practices

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 the patterns that show up once a schema has to survive contact with billing, auditors, and a latency budget.

Try it locally (PostgreSQL 16)

You can reproduce the examples in this lesson on your own machine. Everything shown here was verified against this container.

# Plain PostgreSQL 16 is all this lesson needs.
docker run -d --name pg-demo \
  -e POSTGRES_PASSWORD=demo -e POSTGRES_USER=demo -e POSTGRES_DB=demo \
  -p 5432:5432 postgres:16-alpine

docker exec -it pg-demo psql -U demo -d demo

# Clean up:  docker rm -f pg-demo

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

Start from what the business says it needs: customers place orders containing multiple products, products belong to categories, orders carry a status, and stock has to be tracked per product. Four requirements, four nouns that become entities.

The many-to-many between Order and Product is the one that does not map directly to a table. A row cannot hold a list, so the relationship becomes an entity of its own: OrderItem. That turns out to be a feature rather than a workaround, because the relationship has attributes of its own that belong nowhere else, namely how many were bought and what each cost at the time of the order.

Read the arrows from the "one" side to the "many" side: one Customer has many Orders, one Order has many OrderItems, and one Product appears in many OrderItems. The last two together are what the original many-to-many became. Every foreign key in the diagram points back the other way, from the many side to the one, which is exactly how the REFERENCES clauses fall out in Step 2.

Step 2: Translate to Database Schema

-- Dropped first so this section can be re-run. Children before parents:
-- order_items references orders and products, which reference customers
-- and categories.
DROP TABLE IF EXISTS order_items CASCADE;
DROP TABLE IF EXISTS orders CASCADE;
DROP TABLE IF EXISTS products CASCADE;
DROP TABLE IF EXISTS customers CASCADE;
DROP TABLE IF EXISTS categories CASCADE;

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)
);

Step 3: Query the Model

# Install: pip install "psycopg[binary]"
import psycopg

conn = psycopg.connect("host=localhost dbname=demo user=demo password=demo")
cur = conn.cursor()

# Seed one customer with one two-line order so the query has something
# to return.
cur.execute("INSERT INTO categories (name) VALUES ('Books') RETURNING category_id")
category_id = cur.fetchone()[0]
cur.execute("""INSERT INTO products (category_id, name, price, stock_quantity)
               VALUES (%s, 'Database Design', 39.99, 10), (%s, 'SQL Basics', 24.50, 5)
               RETURNING product_id""", (category_id, category_id))
product_ids = [row[0] for row in cur.fetchall()]

cur.execute("""INSERT INTO customers (email, name) VALUES ('ada@example.com', 'Ada')
               RETURNING customer_id""")
customer_id = cur.fetchone()[0]

cur.execute("""INSERT INTO orders (customer_id, status, total_amount)
               VALUES (%s, 'shipped', 104.48) RETURNING order_id""", (customer_id,))
order_id = cur.fetchone()[0]

cur.executemany("""INSERT INTO order_items (order_id, product_id, quantity, unit_price)
                   VALUES (%s, %s, %s, %s)""",
                [(order_id, product_ids[0], 2, 39.99),
                 (order_id, product_ids[1], 1, 24.50)])
conn.commit()

# Get the customer's order history with product details
query = """
    SELECT
        o.order_id,
        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, p.name
"""

cur.execute(query, (customer_id,))
for row in cur.fetchall():
    print(row)

(1, 'shipped', 'Database Design', 2, Decimal('39.99'), Decimal('79.98'))
(1, 'shipped', 'SQL Basics', 1, Decimal('24.50'), Decimal('24.50'))

One row comes back per line item, not per order, which is what the junction table buys: the many-to-many is traversed in a single query instead of one round trip per product. Note the ordering clause is o.order_date DESC, p.name, not order_date alone. Both of these items share an order and therefore an order_date, so without the tiebreaker their relative order would be whatever the planner happened to produce, and could differ between runs.

Temporal Data Modeling

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

Pattern: Effective Dating

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?

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 2026-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';

-- Stop overlapping periods before they exist. Without this, nothing
-- prevents two rows covering the same instant, and "what was the price
-- on date X" silently returns whichever one sorts first.
CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE subscription_prices
ADD CONSTRAINT no_overlapping_periods
EXCLUDE USING gist (
    subscription_id WITH =,
    tsrange(effective_from, effective_to) WITH &&
);

The partial index is what keeps "get current price" fast no matter how much history accumulates. Confirmed with EXPLAIN ANALYZE on 550,000 rows (50,000 subscriptions with ten historical periods each): the current-price lookup is an Index Scan using idx_current_prices reading three buffers, 0.035 ms. Because the index predicate already pins effective_to, the planner only needs subscription_id as an index condition.

The exclusion constraint is the part most implementations of this pattern leave out, and it is the one that keeps the data trustworthy. && is the range overlap operator, so the constraint reads: no two rows may share a subscription_id and have overlapping [effective_from, effective_to) ranges. It needs the btree_gist extension because a GiST index cannot mix a plain equality column with a range column without it.

-- Give subscription 42 one period, 2020-01-01 to 2020-03-31.
INSERT INTO subscription_prices (subscription_id, price, effective_from, effective_to)
VALUES (42, 19.99, '2020-01-01', '2020-03-31');

-- Now try to add a second period that overlaps it. The constraint,
-- not the application, is what stops this.
INSERT INTO subscription_prices (subscription_id, price, effective_from, effective_to)
VALUES (42, 555.00, '2020-02-01', '2029-01-01');

ERROR:  conflicting key value violates exclusion constraint "no_overlapping_periods"
DETAIL:  Key (subscription_id, tsrange(effective_from, effective_to))=
         (42, ["2020-02-01 00:00:00","2029-01-01 00:00:00")) conflicts with
         existing key (subscription_id, tsrange(effective_from, effective_to))=
         (42, ["2020-01-01 00:00:00","2020-03-31 00:00:00")).

Without that constraint the insert succeeds, and from then on two rows match 2021-06-15. get_price_at_date would still return exactly one row, because of the LIMIT 1, so nothing looks broken: it just answers with whichever period happens to sort first. That is the worst kind of billing bug, silently wrong rather than loudly failing.

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]

These two shapes, "the current value" and "the value as of a date", cover most of what temporal data gets asked. The second one needs no extra index: the primary key is (subscription_id, effective_from), so PostgreSQL walks it backwards and stops at the first match, which EXPLAIN ANALYZE shows as an Index Scan Backward using subscription_prices_pkey at 0.065 ms on the same 550,000-row table. Note the boundary convention: effective_from <= target and effective_to > target make each period half-open, so a change at midnight belongs to the new period and no instant is ever covered twice.

Adding New Price Periods

# Runs on its own against the subscription_prices table created above.
# Install: pip install "psycopg[binary]"
import psycopg

conn = psycopg.connect("host=localhost dbname=demo user=demo password=demo")
cur = conn.cursor()


# Repeated from the previous block so this example stands alone.
def get_current_price(conn, subscription_id):
    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):
    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]


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.
    """
    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


# Usage
cur.execute("""INSERT INTO subscription_prices
                   (subscription_id, price, effective_from, change_reason)
               VALUES (1, 9.99, '2026-01-01', 'initial')""")
conn.commit()

print("current price:", get_current_price(conn, 1))
change_subscription_price(conn, 1, 14.99, '2026-06-01', 'annual increase')
print("after change: ", get_current_price(conn, 1))
print("price on 2026-03-15:", get_price_at_date(conn, 1, '2026-03-15'))
print("price on 2026-07-15:", get_price_at_date(conn, 1, '2026-07-15'))

current price: 9.99
after change:  14.99
price on 2026-03-15: 9.99
price on 2026-07-15: 14.99

Prices are never deleted and never overwritten: the old period is closed by setting its effective_to, and the new price arrives as a new row. The one mutation is that closing write, so the history is append-plus-close rather than strictly immutable, which is what lets you reconstruct any past invoice. The two writes have to be one transaction: fail between them and the subscription either has no current price at all, or two of them. psycopg opens a transaction implicitly and holds it until commit(), so the try/except here is doing real work rather than decoration.

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

-- Start clean so this section can be re-run. Dropping users also drops
-- the audit trigger attached to it; users_audit is independent (it holds
-- no foreign key to users, deliberately, so a deleted user's history
-- survives) and has to be dropped by name.
DROP TABLE IF EXISTS users_audit CASCADE;
DROP TABLE IF EXISTS users CASCADE;

-- 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);

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();

-- Attribute audit rows to the END USER, not the pooled database role.
-- This REPLACES the function above, so it has to keep all three branches:
-- drop the UPDATE or DELETE arm and those changes stop being audited
-- entirely, silently, because the trigger still fires and records nothing.
-- NULLIF is load-bearing too: see the explanation below.
CREATE OR REPLACE FUNCTION audit_users_changes() RETURNS TRIGGER AS $$
DECLARE
    actor TEXT := COALESCE(
        NULLIF(current_setting('app.user_id', true), ''),
        current_user
    );
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', actor, 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', actor, 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', actor, row_to_json(NEW)
        );
        RETURN NEW;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- The application sets it once per request, inside the transaction.
BEGIN;
SET LOCAL app.user_id = 'alice@app';
INSERT INTO users (username, email, role) VALUES ('u1', 'a@x.com', 'user');
COMMIT;

-- A write with no app.user_id set falls back to the database role.
INSERT INTO users (username, email, role) VALUES ('u2', 'b@x.com', 'user');

SELECT username, changed_by FROM users_audit ORDER BY audit_id;

 username | changed_by
----------+------------
 u1       | alice@app
 u2       | demo
(2 rows)

Two things that are easy to get wrong here, both verified above. First, the setting cannot be called app.current_user: current_user is a reserved word, so SET LOCAL app.current_user = '...' fails with syntax error at or near "current_user". Pick a name that avoids the keyword, like app.user_id. Second, and more insidious, COALESCE(current_setting('app.user_id', true), current_user) looks right and is wrong: once any transaction in the session has run SET LOCAL, the setting reverts to an empty string rather than NULL when that transaction ends. So COALESCE never falls through, and every later write is attributed to ''. Wrapping it in NULLIF(..., '') is what makes the fallback actually fire. SET LOCAL (not plain SET) is also the right choice with a connection pool, since it is scoped to the transaction and cannot leak one request's identity into the next.

There is a third trap that is not about attribution at all. CREATE OR REPLACE FUNCTION swaps out the whole function, so the replacement has to carry every branch the original had. Rewrite it with only the INSERT arm and updates and deletes stop being audited: the trigger still fires, the writes still succeed, and nothing is recorded. Nothing errors, and you find out during the audit you were keeping the trail for. Hoisting the actor into a DECLARE block keeps the three branches from drifting apart the next time someone edits one of them.

Querying Audit History

# Runs on its own against the users / users_audit tables and the trigger
# created above.  Install: pip install "psycopg[binary]"
import psycopg

conn = psycopg.connect("host=localhost dbname=demo user=demo password=demo")
cur = conn.cursor()


def get_user_change_history(conn, user_id):
    """Retrieve the 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, audit_id DESC
    """, (user_id,))
    return cur.fetchall()


# Generate something to look at: one INSERT, then one UPDATE that changes
# two columns. The trigger writes an audit row for each, nothing here does.
cur.execute("""INSERT INTO users (username, email, role)
               VALUES ('john', 'john@old.com', 'user')
               RETURNING user_id""")
john_id = cur.fetchone()[0]

cur.execute("""UPDATE users
               SET email = 'john@new.com', role = 'admin'
               WHERE user_id = %s""", (john_id,))
conn.commit()

for row in get_user_change_history(conn, john_id):
    print(row)

(datetime.datetime(2026, 8, 1, 19, 1, 7, 956183), 'UPDATE', 'demo', 'john@old.com', 'john@new.com', 'user', 'admin')
(datetime.datetime(2026, 8, 1, 19, 1, 7, 956183), 'INSERT', 'demo', None, 'john@old.com', None, 'user')

Two rows for one INSERT plus one UPDATE, each showing the field-level before and after. The None values on the INSERT row are correct rather than missing data: there is no previous state to record, so old_values is NULL and ->> yields NULL for every key pulled out of it.

Soft Deletes vs Hard Deletes

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

Implementation: Soft Delete

This section reuses the name users for a differently shaped table than the audit section built, so it starts by dropping the old one. Skipping that produces a confusing pair of errors: the CREATE TABLE reports relation "users" already exists, and then the index statements report column "deleted_at" does not exist, because they are still pointed at the previous table.

-- The audit section above created its own "users" table, with role and
-- updated_at instead of the soft-delete columns. This section replaces it.
-- Without this DROP the CREATE below fails with
--   ERROR:  relation "users" already exists
-- and, more confusingly, the two index statements then fail with
--   ERROR:  column "deleted_at" does not exist
-- because they are still looking at the old table.
-- CASCADE also removes the audit trigger attached to it.
DROP TABLE IF EXISTS users CASCADE;

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    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;

deleted_at IS NULL is the definition of "active", and both indexes are built around it. The first keeps lookups of active users off the deleted ones. The second is the more interesting one: making the UNIQUE index partial is what lets a deleted user's email be registered again, because a plain UNIQUE constraint would keep reserving it forever and reject the new signup with duplicate key value violates unique constraint.

Application Code Patterns

# Runs on its own against the users table created above.
# Install: pip install "psycopg[binary]"
import psycopg

conn = psycopg.connect("host=localhost dbname=demo user=demo password=demo")
cur = conn.cursor()


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()

# Start from an empty table so this block can be re-run. Without the
# TRUNCATE, a second run fails on the partial unique index with
# "duplicate key value violates unique constraint idx_users_email_active",
# because alice and carol are still active and still hold their emails.
# RESTART IDENTITY resets the SERIAL so the ids below are reproducible.
cur.execute("TRUNCATE users RESTART IDENTITY")

cur.execute("""INSERT INTO users (username, email)
               VALUES ('alice', 'alice@example.com'),
                      ('bob',   'bob@example.com'),
                      ('carol', 'carol@example.com')""")
conn.commit()

# Look bob's id up rather than hardcoding it.

cur.execute("SELECT user_id FROM users WHERE username = 'bob'")
bob_id = cur.fetchone()[0]

# The second call returns False: the WHERE clause carries
# "AND deleted_at IS NULL", so re-deleting matches no row and cannot
# quietly overwrite the original deletion timestamp and author.
print("soft delete bob ->", soft_delete_user(conn, bob_id, 'admin'))
print("delete bob again ->", soft_delete_user(conn, bob_id, 'admin'))

print("active users:")
for row in get_active_users(conn):
    print("  ", row)

soft delete bob -> True
delete bob again -> False
active users:
   (1, 'alice', 'alice@example.com')
   (3, 'carol', 'carol@example.com')

The AND deleted_at IS NULL in the UPDATE is doing real work. Without it, deleting an already-deleted user would overwrite the original deleted_at and deleted_by, quietly destroying the record of who removed it and when, which is usually the entire reason for soft-deleting in the first place. With it, the second call matches no row and returns False. The cost of this pattern is that every query now has to remember the filter, and the one that forgets is the one that leaks deleted records into production.

View Pattern for Simplicity

-- A view of the active records only. Note it selects created_at, so the
-- underlying table has to actually have that column.
CREATE VIEW users_active AS
SELECT user_id, username, email, created_at
FROM users
WHERE deleted_at IS NULL;

A view removes that risk by making the filter unforgettable: it is not written in application code at all, so it cannot be left out.

# Application code reads the view and never mentions deleted_at, which is
# the point: the filter cannot be forgotten because it is not written here.
def get_active_users_simple(conn):
    cur = conn.cursor()
    cur.execute("SELECT * FROM users_active ORDER BY username")
    return cur.fetchall()


for row in get_active_users_simple(conn):
    print(row)

(1, 'alice', 'alice@example.com', datetime.datetime(2026, 8, 1, 19, 22, 45, 352786))
(3, 'carol', 'carol@example.com', datetime.datetime(2026, 8, 1, 19, 22, 45, 352786))

Bob is gone from the results while alice and carol remain, and the calling code never mentions deleted_at. Worth knowing before you rely on it: this view is updatable, because PostgreSQL automatically allows INSERT/UPDATE/DELETE on a simple single-table view. A DELETE FROM users_active is therefore a harddelete of the underlying row, exactly what the pattern was meant to prevent. Add WITH CHECK OPTION, or revoke write access to the view, if that matters.

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

Example:
Product category name
copied to products table

Not for speed: an indexed
FK join measured the same
(see Pattern 2 below)

For: cross-service reads,
or freezing the name as
it was at write time

Example:
Per-user order + review
stats, for all users

Before: live 3-table aggregate
over 100k users, 343.1ms
After: materialized view read
of the same 100k rows, 37.9ms

Trade-off accepted ✓

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

The most common denormalization is also the smallest: store a number that could be derived. An order's total is the sum of its line items, so strictly speaking keeping orders.total_amount is redundant. But "derivable" and "cheap to derive" are different things, and every screen that lists orders would otherwise re-aggregate every one of them.

The version below seeds the cached columns by hand so both queries have something to read. That is fine for a demonstration and unacceptable in production: a hand-maintained copy drifts the first time anyone forgets. The trigger in the next section is what makes the pattern safe, and the two belong together.

-- The denormalized shape: the same orders table plus two cached columns.
-- This replaces the normalized version from the schema section above, so
-- both tables are dropped first (order_items first, it has the FK).
DROP TABLE IF EXISTS order_items CASCADE;
DROP TABLE IF EXISTS orders CASCADE;

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

CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(order_id),
    product_id INTEGER,
    quantity INTEGER NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    UNIQUE (order_id, product_id)
);

-- One order with two line items, so both queries below have something to
-- read. The cached columns are seeded by hand here; the trigger in the
-- next section is what keeps them correct from then on.
INSERT INTO orders (order_id, customer_id, total_amount, item_count)
VALUES (12345, 1, 104.48, 2);

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (12345, 101, 2, 39.99),
       (12345, 102, 1, 24.50);

-- Normalized: recompute the sum on every read
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: read the value that is already there
SELECT order_id, total_amount, item_count
FROM orders
WHERE order_id = 12345;

 order_id | total
----------+--------
    12345 | 104.48
(1 row)

 order_id | total_amount | item_count
----------+--------------+------------
    12345 |       104.48 |          2
(1 row)

Both queries return 104.48, which is the point: the two forms are interchangeable to a reader and differ only in how the number was arrived at. Reading the cached total is a single-row primary key lookup, with no join and no aggregation. The trade-off is that total_amount is now a copy that something has to keep in sync every time a line item is added, changed, or removed, which is what the trigger below is for.

On an indexed order_items(order_id) with one order of 5,000 line items, the SUM+JOIN ran in a median 1.2 ms against 0.16 ms for the cached-column read, about 7.5x. The important part is not that number but that the gap is not fixed: it scales with item count, because the join side re-scans more rows every time while the cached read stays a constant-cost single-row fetch. On the same schema that was 21.5x at 20,000 items and 97x at 100,000.

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();

-- Order 12345 already holds two line items totalling 104.48, seeded by
-- hand in the block above. Add a third and let the trigger do the work.
-- Note the line item must reference an existing order: order_items.order_id
-- is a foreign key, so an item for an order that was never created fails
-- with "violates foreign key constraint" and the trigger never runs.
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (12345, 103, 2, 19.99);

SELECT total_amount, item_count FROM orders WHERE order_id = 12345;

-- ...and confirm it maintains the cache on the way back down
DELETE FROM order_items WHERE order_id = 12345 AND product_id = 103;

SELECT total_amount, item_count FROM orders WHERE order_id = 12345;

 total_amount | item_count
--------------+------------
       144.46 |          3
(1 row)

 total_amount | item_count
--------------+------------
       104.48 |          2
(1 row)

The cache stays correct in both directions: adding the third line item moves the order from 104.48 / 2 to 144.46 / 3, and removing it puts the order back to 104.48 / 2 rather than leaving an inflated total behind. That second half is why the function reads COALESCE(NEW.order_id, OLD.order_id): on a DELETE there is no NEW row, so a trigger written against NEW.order_id alone would raise a null-reference error and the totals would silently drift. The tradeoff is that every write to order_items now also writes to orders, so a bulk load of line items pays one extra UPDATE per row and takes a row lock on the parent order, which serializes concurrent inserts into the same order.

Pattern 2: Copying Foreign Key Names

Where Pattern 1 caches a computed number, this one copies a value that already exists somewhere else: the category's name, stored on every product alongside the foreign key that points at it. The stated motive is almost always "to avoid the join", and that motive is usually wrong, which is why this section measures it rather than asserting it.

It is also the pattern with the ugliest failure mode. A cached aggregate has one obvious owner and a trigger can maintain it. A copied name has as many copies as there are child rows, so renaming one category means rewriting every product that referenced it, and any row the update misses is now quietly lying about which category it belongs to.

-- The denormalized shape: products carries a copy of the category name
-- alongside the foreign key. Replaces the normalized products table from
-- the schema section, so drop it first (order_items references it).
DROP TABLE IF EXISTS order_items CASCADE;
DROP TABLE IF EXISTS products CASCADE;

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)
);

-- One category and one product to read, with product_id pinned to 101.
INSERT INTO categories (category_id, name, description)
VALUES (7, 'Books', 'Printed and digital books')
ON CONFLICT (category_id) DO NOTHING;

INSERT INTO products (product_id, name, category_id, category_name, price)
VALUES (101, 'Database Design', 7, 'Books', 39.99);

-- Normalized: fetch the name through the foreign key
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: the name is already on the row
SELECT product_id, name, category_name
FROM products
WHERE product_id = 101;

 product_id |      name       | category_name
------------+-----------------+---------------
        101 | Database Design | Books
(1 row)

 product_id |      name       | category_name
------------+-----------------+---------------
        101 | Database Design | Books
(1 row)

Both forms return the same row, which is exactly why the choice has to be made on grounds other than the result. Measured with EXPLAIN ANALYZE on 50,000 products across 50 categories, the join ran in 0.087-0.107 ms of execution time and the cached-column read in 0.057-0.076 ms: both far under a tenth of a millisecond, and the difference is dwarfed by the network round trip to the client. On a single indexed PostgreSQL instance the join this removes is already effectively free, so raw speed is not a reason to reach for it. Two reasons do hold. The first is that the join may not be available at all, because the categories live in another service or another database. The second is that you may want the name frozen at write time, so that renaming a category does not silently rewrite history, which is the same argument that puts unit_price on order_items earlier in this lesson. If neither applies, keep the join and keep the single source of truth.

Pattern 3: Materialized Views

The first two patterns denormalize a piece of a row: Pattern 1 caches one number per parent, Pattern 2 copies one column across a foreign key. A materialized view scales that idea up to an entire query. It runs the query once and stores the result set as a real table on disk, indexes and all. That is the difference from an ordinary VIEW, which stores only the query text and re-executes it on every read: a plain view is a saved query, a materialized view is a saved answer.

The trade is not really about speed, it is about when the copy is maintained. The trigger in Pattern 1 keeps its cached total correct synchronously, on every write, so a reader can never see a stale number, and every writer pays for that. A materialized view pays nothing on write and is instead wrong the moment the underlying data changes, until someone refreshes it. You are buying read speed with a staleness window, which makes this the right tool for a leaderboard, a nightly report or a dashboard, and the wrong one for an account balance.

The example below computes per-user order and review statistics. It needs enough data for the aggregate to actually cost something, so the setup builds 100,000 users, 500,000 orders and 200,000 reviews. On a handful of rows every approach looks instantaneous and the comparison teaches nothing.

-- Setup for the materialized view example below: a users table plus
-- orders and reviews that both reference it (100,000 users, 500,000
-- orders, 200,000 reviews, randomly distributed).
--
-- This section needs its own shapes for users and orders, different again
-- from the earlier ones, so clear them first. Children before parents, or
-- the foreign keys block the drop.
DROP TABLE IF EXISTS reviews CASCADE;
DROP TABLE IF EXISTS order_items CASCADE;
DROP TABLE IF EXISTS orders CASCADE;
DROP TABLE IF EXISTS users CASCADE;

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES users(user_id),
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10, 2) NOT NULL
);
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

CREATE TABLE reviews (
    review_id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(user_id),
    rating INTEGER NOT NULL
);
CREATE INDEX idx_reviews_user_id ON reviews(user_id);

INSERT INTO users (username)
SELECT 'user' || g FROM generate_series(1, 100000) g;

INSERT INTO orders (customer_id, order_date, total_amount)
SELECT (random() * 99999 + 1)::int,
       -- Multiply an interval rather than building one from a string.
       -- "(random() * 365 || ' days')::interval" works almost always and
       -- then dies on the one row where the float is small enough that
       -- PostgreSQL renders it in scientific notation:
       --   invalid input syntax for type interval: "1.71e-06 days"
       now() - (random() * 365) * INTERVAL '1 day',
       (random() * 500 + 10)::decimal(10, 2)
FROM generate_series(1, 500000) g;

INSERT INTO reviews (user_id, rating)
SELECT (random() * 99999 + 1)::int, (random() * 4 + 1)::int
FROM generate_series(1, 200000) g;

-- Expensive query: per-user order + review statistics, for every user
--
-- A naive version LEFT JOINs orders and reviews to users in the same
-- query. Don't write it that way: joining two independent one-to-many
-- tables at the same level multiplies rows before the aggregate runs
-- (a "fan-out"). COUNT(DISTINCT ...) still comes out right because it
-- de-duplicates, but SUM() does not: a user with 2 orders ($10, $20) and
-- 3 reviews would fan out to 6 joined rows, and SUM(o.total_amount) would
-- come out as $90, not the correct $30 (each order counted once per
-- matching review). Aggregate each side in its own subquery first, then
-- join the already-aggregated one-row-per-user results:
CREATE MATERIALIZED VIEW user_stats AS
SELECT
    u.user_id,
    u.username,
    COALESCE(o.total_orders, 0) AS total_orders,
    COALESCE(o.lifetime_value, 0) AS lifetime_value,
    o.last_order_date,
    COALESCE(r.review_count, 0) AS review_count,
    r.avg_rating
FROM users u
LEFT JOIN (
    SELECT customer_id,
           COUNT(*) AS total_orders,
           SUM(total_amount) AS lifetime_value,
           MAX(order_date) AS last_order_date
    FROM orders
    GROUP BY customer_id
) o ON u.user_id = o.customer_id
LEFT JOIN (
    SELECT user_id, COUNT(*) AS review_count, AVG(rating) AS avg_rating
    FROM reviews
    GROUP BY user_id
) r ON u.user_id = r.user_id;

CREATE INDEX idx_user_stats_user ON user_stats(user_id);

-- A single user_id lookup is not the interesting case: PostgreSQL pushes
-- the "user_id = 42" filter into both subqueries and uses the indexes on
-- orders(customer_id) and reviews(user_id), so the live version already
-- runs in well under a millisecond (measured: 0.15-0.18 ms via EXPLAIN
-- ANALYZE, execution time only). The materialized view lookup lands in
-- the same comfortably-sub-millisecond band once you include a round
-- trip to the client (measured: 0.3-0.5 ms), so a single-row read is
-- NOT where the view pays off either way. The materialized view earns
-- its keep when you need this for EVERY user at once, e.g. a leaderboard
-- or nightly report. Measured on 100,000 users, 500,000 orders, 200,000
-- reviews (via psql \timing, full 100,000-row result set both times):

-- The "live" side: the same query the view was defined from, run directly
SELECT
    u.user_id, u.username,
    COALESCE(o.total_orders, 0) AS total_orders,
    COALESCE(o.lifetime_value, 0) AS lifetime_value,
    o.last_order_date,
    COALESCE(r.review_count, 0) AS review_count,
    r.avg_rating
FROM users u
LEFT JOIN (
    SELECT customer_id, COUNT(*) AS total_orders,
           SUM(total_amount) AS lifetime_value, MAX(order_date) AS last_order_date
    FROM orders GROUP BY customer_id
) o ON u.user_id = o.customer_id
LEFT JOIN (
    SELECT user_id, COUNT(*) AS review_count, AVG(rating) AS avg_rating
    FROM reviews GROUP BY user_id
) r ON u.user_id = r.user_id;
-- Live 3-table aggregate over all users: 343.1 ms

SELECT * FROM user_stats;
-- Reading the precomputed view: 37.9 ms  (about 9x faster than the live query above)

SELECT * FROM user_stats WHERE user_id = 42;
-- 0.3-0.5 ms (a single-row read of a flat, pre-joined table; not
-- meaningfully different from the live query above for one row)

Reading the stored result for all 100,000 users came out about 9x faster than re-running the live aggregate: 37.9 ms against 343.1 ms in the run above, and 35.6 ms against 322.6 ms when re-measured on different hardware, so the ratio reproduces even though the absolute numbers do not. Note where that gain does notappear: fetching a single user is already sub-millisecond either way, because PostgreSQL pushes the user_id filter down into both subqueries and uses the indexes on orders(customer_id) and reviews(user_id). A materialized view earns its keep on the whole-table read, not the single-row lookup, which is the same lesson as Pattern 2 arriving from a different direction: measure the query you actually run.

Refreshing Materialized Views

-- Manual refresh. Takes ACCESS EXCLUSIVE on the view: every reader
-- blocks until it finishes.
REFRESH MATERIALIZED VIEW user_stats;

-- Concurrent refresh: readers keep working throughout. It requires a
-- UNIQUE index on the view, and says so if one is missing:
--   ERROR:  cannot refresh materialized view "public.user_stats" concurrently
--   HINT:   Create a unique index with no WHERE clause on one or more
--           columns of the materialized view.
CREATE UNIQUE INDEX idx_user_stats_user_unique ON user_stats(user_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats;

The plain REFRESH takes ACCESS EXCLUSIVE on the view, so every reader blocks until it completes: on a view that takes 300 ms to rebuild, that is 300 ms of stalled queries. CONCURRENTLY trades that for a slower rebuild that readers can work through, and it is not optional to have the unique index, PostgreSQL refuses outright without one.

# Schedule periodic refreshes.  Install: pip install schedule "psycopg[binary]"
import time
from datetime import datetime   # needed by the log line below

import psycopg
import schedule


def refresh_user_stats():
    # A fresh connection per run, closed by the context manager, so a
    # long-lived scheduler never sits on an idle connection.
    with psycopg.connect("host=localhost dbname=demo user=demo password=demo") as conn:
        with conn.cursor() as cur:
            cur.execute("REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats")
    print(f"Refreshed user_stats at {datetime.now()}")


schedule.every(1).hours.do(refresh_user_stats)

# Registering the job does not run it. Without this loop the process
# exits immediately and nothing is ever refreshed.
while True:
    schedule.run_pending()
    time.sleep(60)

Registering a job with schedule does not run it. Without the run_pending() loop the process registers the job and exits immediately, and nothing is ever refreshed. Note also what the schedule really buys you: hourly refresh means readers see data up to an hour stale, which is fine for a leaderboard and not fine for an account balance. For anything beyond a single process, schedule is the wrong tool anyway, since two instances of the app mean two timers and two refreshes; use a single scheduler (cron, a job runner, or pg_cron inside the database) instead.

Modeling Best Practices Checklist

Modeling Decision Tree

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