Database Design Patterns

Database Design Patterns

Designing efficient database schemas, indexing strategies, migration workflows, and data access patterns for SQL and NoSQL databases.

When to Use

• Designing a new database schema from scratch
• Optimizing slow queries with proper indexing
• Planning database migrations without downtime
• Choosing between SQL, NoSQL, or hybrid approaches
• Modeling complex domain relationships in relational databases

Schema Design Patterns

Single Table Inheritance

CREATE TABLE assets (
    id SERIAL PRIMARY KEY,
    type VARCHAR(20) NOT NULL CHECK (type IN ('document', 'image', 'video')),
    title TEXT NOT NULL,
    page_count INTEGER,
    width INTEGER, height INTEGER,
    duration INTEGER, resolution VARCHAR(20),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Concrete Table Inheritance

CREATE TABLE documents (
    id SERIAL PRIMARY KEY, title TEXT NOT NULL, page_count INTEGER
);
CREATE TABLE images (
    id SERIAL PRIMARY KEY, title TEXT NOT NULL, width INTEGER, height INTEGER
);

Class Table Inheritance

CREATE TABLE assets (
    id SERIAL PRIMARY KEY, type VARCHAR(20) NOT NULL, title TEXT NOT NULL
);
CREATE TABLE documents (asset_id INTEGER PRIMARY KEY REFERENCES assets(id), page_count INTEGER);
CREATE TABLE images (asset_id INTEGER PRIMARY KEY REFERENCES assets(id), width INTEGER, height INTEGER);

Indexing Patterns

Composite Index Column Order

-- For: WHERE status = 'active' AND created_at > '2024-01-01'
-- Place high-selectivity columns FIRST
CREATE INDEX idx_orders_status_created ON orders (status, created_at);

Covering Index

-- Index-Only Scan: never touches the heap
-- For: SELECT user_id, email FROM users WHERE status = 'active';
CREATE INDEX idx_users_status_covering ON users (status) INCLUDE (user_id, email);

Partial Index

-- Index only active records (much smaller, faster writes)
CREATE INDEX idx_orders_active ON orders (created_at) WHERE status = 'active';

Expression Index

CREATE INDEX idx_users_lower_email ON users (LOWER(email));
-- SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

Migration Patterns

Expand-Contract (Zero-Downtime)

-- Phase 1: EXPAND — add new column
ALTER TABLE users ADD COLUMN email_new VARCHAR(255);
-- App writes to both columns

-- Phase 2: Backfill
UPDATE users SET email_new = email WHERE email_new IS NULL;

-- Phase 3: CONTRACT
ALTER TABLE users DROP COLUMN email;
ALTER TABLE users RENAME COLUMN email_new TO email;

NoSQL Patterns

Document Denormalization

user = {
    "_id": "user_123",
    "name": "Alice",
    "addresses": [
        {"type": "home", "street": "123 Main St"},
        {"type": "work", "street": "456 Corp Ave"}
    ]
}
# Pro: Single read for user + addresses
# Con: Update anomalies on separately-edited embedded docs

CQRS

class CQRSHandler:
    def __init__(self):
        self.write_db = PostgresWriteDB()     # Normalized
        self.read_db = ElasticsearchReadDB()   # Denormalized
    
    def handle_command(self, command):
        result = self.write_db.execute(command)
        self.sync_to_read_model(result)
        return result
    
    def handle_query(self, query):
        return self.read_db.search(query)

Common Pitfalls

1. Premature denormalization — normalize first, denormalize only when query patterns demand it
2. Missing foreign keys — relational integrity enforced at app level drifts over time
3. Index-everything — writes become slow; index only what queries filter on
4. SELECT * in production — always name columns; breaks when schema changes
5. No migration plan — schema changes without backfill cause downtime
6. Ignoring EXPLAIN ANALYZE — the optimizer shows what's really happening; always check

Verification Checklist

• Schema normalized to 3NF before any denormalization
• Indexes match actual query patterns (check slow query log)
• Migrations are reversible (up + down functions)
• Foreign keys enforced at DB level
• EXPLAIN ANALYZE confirms index usage on critical queries
• No N+1 queries in common access patterns

See Also

• sql-query-optimization — optimizing queries against your schema
• data-structures-algorithms — choosing right data structures
• system-design-patterns — database at scale