database-management-patterns

What this skill does

# 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:**
```sql
-- 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:**
```sql
-- 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:**
```sql
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:**
```sql
-- 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:**
```sql
-- 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:**
```sql
CREATE OR REPLACE FUNC