Database Design Patterns I Wish I Knew Earlier

Database design mistakes are expensive to fix later. After working with various database systems and refactoring schemas multiple times, I've learned some patterns that can save significant pain down the road.

1. The Audit Trail Pattern

Always include these fields in your tables:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    -- Business fields here
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by INTEGER REFERENCES users(id),
    updated_by INTEGER REFERENCES users(id),
    is_deleted BOOLEAN DEFAULT FALSE
);

This pattern has saved me countless times when debugging issues or implementing soft deletes. You'll thank yourself later.

2. Polymorphic Associations

When you need to associate records with different types of entities:

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    commentable_type VARCHAR(50) NOT NULL,
    commentable_id INTEGER NOT NULL,
    user_id INTEGER REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Index for efficient queries
CREATE INDEX idx_comments_polymorphic 
ON comments(commentable_type, commentable_id);

This allows comments on posts, products, or any other entity without creating separate comment tables for each.

3. The Status Machine Pattern

Instead of boolean flags, use status enums for better state management:

CREATE TYPE order_status AS ENUM (
    'pending',
    'confirmed',
    'processing',
    'shipped',
    'delivered',
    'cancelled',
    'refunded'
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status order_status DEFAULT 'pending',
    status_changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

This approach makes state transitions explicit and prevents invalid states.

4. Denormalization for Performance

Sometimes breaking normalization rules improves performance significantly:

Key Takeaways

Good database design is about finding the right balance between normalization, performance, and maintainability. These patterns have served me well across different projects and database systems. The key is to think about future requirements and plan for change from the beginning.