Database Management Patterns

Database Management Patterns

A comprehensive skill for mastering database management across SQL (PostgreSQL) and NoSQL (MongoDB) systems. This skill covers schema design, indexing strategies, transaction management, replication, sharding, and performance optimization for production-grade applications.

When to Use This Skill

Use this skill when:

• Designing database schemas for new applications or refactoring existing ones
• Choosing between SQL and NoSQL databases for your use case
• Optimizing query performance with proper indexing strategies
• Implementing data consistency with transactions and ACID guarantees
• Scaling databases horizontally with sharding and replication
• Managing high-traffic applications requiring distributed databases
• Ensuring data integrity with constraints, triggers, and validation
• Troubleshooting performance issues using explain plans and query analysis
• Building fault-tolerant systems with replication and failover strategies
• Working with complex data relationships (relational) or flexible schemas (document)

Core Concepts

Database Paradigms Comparison

Relational Databases (PostgreSQL)

Strengths:

• ACID Transactions: Strong consistency guarantees
• Complex Queries: JOIN operations, subqueries, CTEs
• Data Integrity: Foreign keys, constraints, triggers
• Normalized Data: Reduced redundancy, consistent updates
• Mature Ecosystem: Rich tooling, extensions, community

Best For:

• Financial systems requiring strict consistency
• Complex relationships and data integrity requirements
• Applications with structured, well-defined schemas
• Systems requiring complex analytical queries
• Multi-step transactions across multiple tables

Document Databases (MongoDB)

Strengths:

• Flexible Schema: Easy schema evolution, polymorphic data
• Horizontal Scalability: Built-in sharding support
• JSON-Native: Natural fit for modern application development
• Embedded Documents: Denormalized data for performance
• Aggregation Framework: Powerful data processing pipeline

Best For:

• Rapidly evolving applications with changing requirements
• Content management systems with varied data structures
• Real-time analytics and event logging
• Mobile and web applications with JSON APIs
• Hierarchical or nested data structures

ACID Properties

Atomicity: All operations in a transaction succeed or fail together Consistency: Transactions bring database from one valid state to another Isolation: Concurrent transactions don't interfere with each other Durability: Committed transactions survive system failures

CAP Theorem

In distributed systems, choose two of three:

• Consistency: All nodes see the same data
• Availability: System remains operational
• Partition Tolerance: System continues despite network failures

PostgreSQL emphasizes CP (Consistency + Partition Tolerance) MongoDB can be configured for CP or AP depending on write/read concerns

PostgreSQL Patterns

Schema Design Fundamentals

Normalization Levels

First Normal Form (1NF)

• Atomic values (no arrays or lists in columns)
• Each row is unique (primary key exists)
• No repeating groups

Second Normal Form (2NF)

• Meets 1NF requirements
• All non-key attributes depend on the entire primary key

Third Normal Form (3NF)

• Meets 2NF requirements
• No transitive dependencies (non-key attributes depend only on primary key)

When to Denormalize:

• Read-heavy workloads where joins are expensive
• Frequently accessed aggregate data
• Historical snapshots that shouldn't change
• Performance-critical queries

Table Design Patterns

Primary Keys:

-- Serial auto-increment (traditional)
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- UUID for distributed systems
CREATE TABLE accounts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Composite primary key
CREATE TABLE order_items (
    order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

Foreign Key Constraints:

-- Cascade delete: Remove child records when parent deleted
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Set null: Preserve child records, nullify reference
CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INTEGER,
    user_id INTEGER,
    content TEXT NOT NULL,
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE SET NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Restrict: Prevent deletion if child records exist
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    category_id INTEGER NOT NULL,
    name VARCHAR(255) NOT NULL,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT
);

Advanced Constraints

Check Constraints:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price NUMERIC(10, 2) NOT NULL CHECK (price > 0),
    discount_percent INTEGER CHECK (discount_percent BETWEEN 0 AND 100),
    stock_quantity INTEGER NOT NULL CHECK (stock_quantity >= 0)
);

-- Table-level check constraint
CREATE TABLE date_ranges (
    id SERIAL PRIMARY KEY,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    CHECK (end_date > start_date)
);

Unique Constraints:

-- Single column unique
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL
);

-- Composite unique constraint
CREATE TABLE user_permissions (
    user_id INTEGER NOT NULL,
    permission_id INTEGER NOT NULL,
    granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (user_id, permission_id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (permission_id) REFERENCES permissions(id)
);

-- Partial unique index (unique where condition met)
CREATE UNIQUE INDEX unique_active_email
ON users (email)
WHERE active = true;

Triggers and Functions

Audit Trail Pattern:

-- Audit table
CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(255) NOT NULL,
    record_id INTEGER NOT NULL,
    action VARCHAR(10) NOT NULL,
    old_data JSONB,
    new_data JSONB,
    changed_by VARCHAR(255),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Trigger function
CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log (table_name, record_id, action, new_data, changed_by)
        VALUES (TG_TABLE_NAME, NEW.id, 'INSERT', row_to_json(NEW), current_user);
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (table_name, record_id, action, old_data, new_data, changed_by)
        VALUES (TG_TABLE_NAME, NEW.id, 'UPDATE', row_to_json(OLD), row_to_json(NEW), current_user);
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (table_name, record_id, action, old_data, changed_by)
        VALUES (TG_TABLE_NAME, OLD.id, 'DELETE', row_to_json(OLD), current_user);
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

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

Timestamp Update Pattern: