Stage 3 · Build

Relational Databases (PostgreSQL)

Schema Design Patterns

Normalization, denormalization, soft deletes, audit tables, and migrations.

7 min readDatabases for SREBuild

Design Principles

Good schema design balances normalization with query performance. Start normalized, then denormalize based on measured query patterns. A schema that is too normalized creates complex JOINs; one that is too denormalized creates update anomalies.

Approach	Pros	Cons
Normalized (3NF)	No redundancy, easy updates	Complex JOINs for reads
Denormalized	Fast reads, simple queries	Redundancy, complex writes
Hybrid	Best of both	Requires careful planning

Start normalized, denormalize with evidence

Never denormalize speculatively. Profile your actual queries, identify JOIN bottlenecks, and denormalize only where measurements show it is necessary. Most applications run perfectly on normalized schemas.

Naming Conventions

Use snake_case for table and column names — orders_items not OrdersItems
Use singular table names — user not users (debatable, but be consistent)
Primary keys: always id (or表名_id for composite keys)
Foreign keys: always referenced_table_id — user_id not user
Timestamps: created_at, updated_at with DEFAULT NOW()
Boolean flags: is_active, has_permission, can_edit

Consistent naming pattern


CREATE TABLE teams (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE team_members (
    id SERIAL PRIMARY KEY,
    team_id INTEGER REFERENCES teams(id) ON DELETE CASCADE,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    role VARCHAR(20) NOT NULL DEFAULT 'member',
    joined_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(team_id, user_id)
);

The foreign keys team_id and user_id immediately tell you which tables they reference. The UNIQUE constraint prevents duplicate memberships.

Soft Deletes

Soft deletes mark rows as deleted instead of removing them. This preserves data for audit trails, recovery, and historical queries. The tradeoff is increased storage and query complexity.

Soft delete pattern


-- Add deleted_at column
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP;

-- Soft delete
UPDATE users SET deleted_at = NOW() WHERE id = 42;

-- Query only active users
CREATE VIEW active_users AS
SELECT * FROM users WHERE deleted_at IS NULL;

-- Partial index for performance
CREATE INDEX idx_users_active ON users(email)
WHERE deleted_at IS NULL;

-- Restore a soft-deleted user
UPDATE users SET deleted_at = NULL WHERE id = 42;

The partial index only indexes active users, keeping the index small. The view provides a clean interface for application code that should never see deleted records.

Soft deletes complicate foreign keys

Foreign keys reference rows by primary key, regardless of deleted_at. A soft-deleted user still has foreign key references. If you need referential integrity for active records, use application-level checks or database triggers.

Audit Logging

Audit tables track who changed what and when. They are essential for compliance (SOC2, GDPR) and debugging data issues.

Audit log with trigger


CREATE TABLE audit_log (
    id BIGSERIAL PRIMARY KEY,
    table_name TEXT NOT NULL,
    record_id INTEGER NOT NULL,
    action TEXT NOT NULL,  -- INSERT, UPDATE, DELETE
    old_data JSONB,
    new_data JSONB,
    changed_by TEXT DEFAULT current_user,
    changed_at TIMESTAMP DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION audit_trigger_fn()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log (table_name, record_id, action, new_data)
        VALUES (TG_TABLE_NAME, NEW.id, 'INSERT', to_jsonb(NEW));
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (table_name, record_id, action, old_data, new_data)
        VALUES (TG_TABLE_NAME, NEW.id, 'UPDATE', to_jsonb(OLD), to_jsonb(NEW));
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (table_name, record_id, action, old_data)
        VALUES (TG_TABLE_NAME, OLD.id, 'DELETE', to_jsonb(OLD));
    END IF;
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_users
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION audit_trigger_fn();

This trigger automatically logs every change to the users table. The old_data and new_data columns store the complete row state as JSONB, making it easy to search and compare.

When to Use JSONB

JSONB in PostgreSQL stores JSON documents with indexing support. Use it for semi-structured data where the schema varies or evolves — not as a replacement for proper relational design.

JSONB patterns


-- Store variable attributes per product type
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    type VARCHAR(50) NOT NULL,
    attributes JSONB NOT NULL DEFAULT '{}'::jsonb
);

-- Insert with type-specific attributes
INSERT INTO products (name, type, attributes) VALUES
('iPhone 15', 'phone', '{"storage": 128, "color": "black"}'),
('Standing Desk', 'furniture', '{"height_min": 28, "height_max": 48}');

-- Query JSONB fields
SELECT name, attributes->>'storage' AS storage
FROM products WHERE type = 'phone';

-- GIN index for containment queries
CREATE INDEX idx_products_attrs ON products USING GIN(attributes);
SELECT * FROM products WHERE attributes @> '{"color": "black"}';

The GIN index enables fast containment queries on JSONB. Use JSONB for attributes that vary by type, user preferences, or API response caching — not for data that should be queried relationally.

Migration Strategy

Database migrations are version-controlled schema changes. Every schema change should be a migration file that can be applied and rolled back.

Never edit production schema manually — always use migrations
Each migration should be idempotent (safe to run multiple times)
Migrations must be backward-compatible for zero-downtime deploys
Test migrations against a copy of production data
Keep migrations small — one logical change per migration

Use a migration framework

Flyway, Alembic (Python), ActiveRecord (Ruby), and golang-migrate are battle-tested tools. They track applied migrations, support rollback, and prevent accidental double-application.

Mark this lesson complete to store local progress and unlock a cleaner resume path the next time you visit.