FerranGuardia/skills/backend/database-designer

Database Designer - POWERFUL Tier Skill

Overview

A comprehensive database design skill that provides expert-level analysis, optimization, and migration capabilities for modern database systems. This skill combines theoretical principles with practical tools to help architects and developers create scalable, performant, and maintainable database schemas.

Core Competencies

Schema Design & Analysis

• Normalization Analysis: Automated detection of normalization levels (1NF through BCNF)
• Denormalization Strategy: Smart recommendations for performance optimization
• Data Type Optimization: Identification of inappropriate types and size issues
• Constraint Analysis: Missing foreign keys, unique constraints, and null checks
• Naming Convention Validation: Consistent table and column naming patterns
• ERD Generation: Automatic Mermaid diagram creation from DDL

Index Optimization

• Index Gap Analysis: Identification of missing indexes on foreign keys and query patterns
• Composite Index Strategy: Optimal column ordering for multi-column indexes
• Index Redundancy Detection: Elimination of overlapping and unused indexes
• Performance Impact Modeling: Selectivity estimation and query cost analysis
• Index Type Selection: B-tree, hash, partial, covering, and specialized indexes

Migration Management

• Zero-Downtime Migrations: Expand-contract pattern implementation
• Schema Evolution: Safe column additions, deletions, and type changes
• Data Migration Scripts: Automated data transformation and validation
• Rollback Strategy: Complete reversal capabilities with validation
• Execution Planning: Ordered migration steps with dependency resolution

Database Design Principles

Normalization Forms

First Normal Form (1NF)

• Atomic Values: Each column contains indivisible values
• Unique Column Names: No duplicate column names within a table
• Uniform Data Types: Each column contains the same type of data
• Row Uniqueness: No duplicate rows in the table

Example Violation:

-- BAD: Multiple phone numbers in one column
CREATE TABLE contacts (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    phones VARCHAR(200)  -- "123-456-7890, 098-765-4321"
);

-- GOOD: Separate table for phone numbers
CREATE TABLE contacts (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE contact_phones (
    id INT PRIMARY KEY,
    contact_id INT REFERENCES contacts(id),
    phone_number VARCHAR(20),
    phone_type VARCHAR(10)
);

Second Normal Form (2NF)

• 1NF Compliance: Must satisfy First Normal Form
• Full Functional Dependency: Non-key attributes depend on the entire primary key
• Partial Dependency Elimination: Remove attributes that depend on part of a composite key

Example Violation:

-- BAD: Student course table with partial dependencies
CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    student_name VARCHAR(100),  -- Depends only on student_id
    course_name VARCHAR(100),   -- Depends only on course_id
    grade CHAR(1),
    PRIMARY KEY (student_id, course_id)
);

-- GOOD: Separate tables eliminate partial dependencies
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE courses (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE enrollments (
    student_id INT REFERENCES students(id),
    course_id INT REFERENCES courses(id),
    grade CHAR(1),
    PRIMARY KEY (student_id, course_id)
);

Third Normal Form (3NF)

• 2NF Compliance: Must satisfy Second Normal Form
• Transitive Dependency Elimination: Non-key attributes should not depend on other non-key attributes
• Direct Dependency: Non-key attributes depend directly on the primary key

Example Violation:

-- BAD: Employee table with transitive dependency
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    department_name VARCHAR(100),  -- Depends on department_id, not employee id
    department_budget DECIMAL(10,2) -- Transitive dependency
);

-- GOOD: Separate department information
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    budget DECIMAL(10,2)
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT REFERENCES departments(id)
);

Boyce-Codd Normal Form (BCNF)

• 3NF Compliance: Must satisfy Third Normal Form
• Determinant Key Rule: Every determinant must be a candidate key
• Stricter 3NF: Handles anomalies not covered by 3NF

Denormalization Strategies

When to Denormalize

1. Read-Heavy Workloads: High query frequency with acceptable write trade-offs
2. Performance Bottlenecks: Join operations causing significant latency
3. Aggregation Needs: Frequent calculation of derived values
4. Caching Requirements: Pre-computed results for common queries

Common Denormalization Patterns

Redundant Storage

-- Store calculated values to avoid expensive joins
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT REFERENCES customers(id),
    customer_name VARCHAR(100), -- Denormalized from customers table
    order_total DECIMAL(10,2),  -- Denormalized calculation
    created_at TIMESTAMP
);

Materialized Aggregates

-- Pre-computed summary tables
CREATE TABLE customer_statistics (
    customer_id INT PRIMARY KEY,
    total_orders INT,
    lifetime_value DECIMAL(12,2),
    last_order_date DATE,
    updated_at TIMESTAMP
);

Index Optimization Strategies

B-Tree Indexes

• Default Choice: Best for range queries, sorting, and equality matches
• Column Order: Most selective columns first for composite indexes
• Prefix Matching: Supports leading column subset queries
• Maintenance Cost: Balanced tree structure with logarithmic operations

Hash Indexes

• Equality Queries: Optimal for exact match lookups
• Memory Efficiency: Constant-time access for single-value queries
• Range Limitations: Cannot support range or partial matches
• Use Cases: Primary keys, unique constraints, cache keys

Composite Indexes

-- Query pattern determines optimal column order
-- Query: WHERE status = 'active' AND created_date > '2023-01-01' ORDER BY priority DESC
CREATE INDEX idx_task_status_date_priority 
ON tasks (status, created_date, priority DESC);

-- Query: WHERE user_id = 123 AND category IN ('A', 'B') AND date_field BETWEEN '...' AND '...'
CREATE INDEX idx_user_category_date 
ON user_activities (user_id, category, date_field);

Covering Indexes

-- Include additional columns to avoid table lookups
CREATE INDEX idx_user_email_covering 
ON users (email) 
INCLUDE (first_name, last_name, status);

-- Query can be satisfied entirely from the index
-- SELECT first_name, last_name, status FROM users WHERE email = '[email protected]';

Partial Indexes

-- Index only relevant subset of data
CREATE INDEX idx_active_users_email 
ON users (email) 
WHERE status = 'active';

-- Index for recent orders only
CREATE INDEX idx_recent_orders_customer 
ON orders (customer_id, created_at) 
WHERE created_at > CURRENT_DATE - INTERVAL '30 days';

Query Analysis & Optimization

Query Patterns Recognition

1. Equality Filters: Single-column B-tree indexes
2. Range Queries: B-tree with proper column ordering
3. Text Search: Full-text indexes or trigram indexes
4. Join Operations: Foreign key indexes on both sides
5. Sorting Requirements: Indexes matching ORDER BY clauses

Index Selection Algorithm

1. Identify WHERE clause columns
2. Determine most selective columns first
3. Consider JOIN conditions
4. Include ORDER BY columns if possible
5. Evaluate covering index opportunities
6. Check for existing overlapping indexes

Data Modeling Patterns

Star Schema (Data Warehousing)

-- Central fact table
CREATE TABLE sales_facts (
    sale_id BIGINT PRIMARY KEY,
    product_id INT REFERENCES products(id),
    customer_id INT REFERENCES customers(id),
    date_id INT REFERENCES date_dimension(id),
    store_id INT REFERENCES stores(id),
    quantity INT,
    unit_price DECIMAL(8,2),
    total_amount DECIMAL(10,2)
);

-- Dimension tables
CREATE TABLE date_dimension (
    id INT PRIMARY KEY,
    date_value DATE,
    year INT,
    quarter INT,
    month INT,
    day_of_week INT,
    is_weekend BOOLEAN
);

Snowflake Schema

-- Normalized dimension tables
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(200),
    category_id INT REFERENCES product_categories(id),
    brand_id INT REFERENCES brands(id)
);

CREATE TABLE product_categories (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    parent_category_id INT REFERENCES product_categories(id)
);

Document Model (JSON Storage)

-- Flexible document storage with indexing
CREATE TABLE documents (
    id UUID PRIMARY KEY,
    document_type VARCHAR(50),
    data JSONB,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Index on JSON properties
CREATE INDEX idx_documents_user_id 
ON documents USING GIN ((data->>'user_id'));

CREATE INDEX idx_documents_status 
ON documents ((data->>'status')) 
WHERE document_type = 'order';

Graph Data Patterns

-- Adjacency list for hierarchical data
CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    parent_id INT REFERENCES categories(id),
    level INT,
    path VARCHAR(500)  -- Materialized path: "/1/5/12/"
);

-- Many-to-many relationships
CREATE TABLE relationships (
    id UUID PRIMARY KEY,
    from_entity_id UUID,
    to_entity_id UUID,
    relationship_type VARCHAR(50),
    created_at TIMESTAMP,
    INDEX (from_entity_id, relationship_type),
    INDEX (to_entity_id, relationship_type)
);

Migration Strategies

Zero-Downtime Migration (Expand-Contract Pattern)

Phase 1: Expand

-- Add new column without constraints
ALTER TABLE users ADD COLUMN new_email VARCHAR(255);

-- Backfill data in batches
UPDATE users SET new_email = email WHERE id BETWEEN 1 AND 1000;
-- Continue in batches...

-- Add constraints after backfill
ALTER TABLE users ADD CONSTRAINT users_new_email_unique UNIQUE (new_email);
ALTER TABLE users ALTER COLUMN new_email SET NOT NULL;

Phase 2: Contract

-- Update application to use new column
-- Deploy application changes
-- Verify new column is being used

-- Remove old column
ALTER TABLE users DROP COLUMN email;
-- Rename new column
ALTER TABLE users RENAME COLUMN new_email TO email;

Data Type Changes

-- Safe string to integer conversion
ALTER TABLE products ADD COLUMN sku_number INTEGER;
UPDATE products SET sku_number = CAST(sku AS INTEGER) WHERE sku ~ '^[0-9]+$';
-- Validate conversion success before dropping old column

Partitioning Strategies

Horizontal Partitioning (Sharding)

-- Range partitioning by date
CREATE TABLE sales_2023 PARTITION OF sales
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE sales_2024 PARTITION OF sales
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- Hash partitioning by user_id
CREATE TABLE user_data_0 PARTITION OF user_data
FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE user_data_1 PARTITION OF user_data
FOR VALUES WITH (MODULUS 4, REMAINDER 1);

Vertical Partitioning

-- Separate frequently accessed columns
CREATE TABLE users_core (
    id INT PRIMARY KEY,
    email VARCHAR(255),
    status VARCHAR(20),
    created_at TIMESTAMP
);

-- Less frequently accessed profile data
CREATE TABLE users_profile (
    user_id INT PRIMARY KEY REFERENCES users_core(id),
    bio TEXT,
    preferences JSONB,
    last_login TIMESTAMP
);

Connection Management

Connection Pooling

• Pool Size: CPU cores × 2 + effective spindle count
• Connection Lifetime: Rotate connections to prevent resource leaks
• Timeout Settings: Connection, idle, and query timeouts
• Health Checks: Regular connection validation

Read Replicas Strategy

-- Write queries to primary
INSERT INTO users (email, name) VALUES ('[email protected]', 'John Doe');

-- Read queries to replicas (with appropriate read preference)
SELECT * FROM users WHERE status = 'active';  -- Route to read replica

-- Consistent reads when required
SELECT * FROM users WHERE id = LAST_INSERT_ID();  -- Route to primary

Caching Layers

Cache-Aside Pattern

def get_user(user_id):
    # Try cache first
    user = cache.get(f"user:{user_id}")
    if user is None:
        # Cache miss - query database
        user = db.query("SELECT * FROM users WHERE id = %s", user_id)
        # Store in cache
        cache.set(f"user:{user_id}", user, ttl=3600)
    return user

Write-Through Cache

• Consistency: Always keep cache and database in sync
• Write Latency: Higher due to dual writes
• Data Safety: No data loss on cache failures

Cache Invalidation Strategies

1. TTL-Based: Time-based expiration
2. Event-Driven: Invalidate on data changes
3. Version-Based: Use version numbers for consistency
4. Tag-Based: Group related cache entries

Database Selection Guide

SQL Databases

PostgreSQL

• Strengths: ACID compliance, complex queries, JSON support, extensibility
• Use Cases: OLTP applications, data warehousing, geospatial data
• Scale: Vertical scaling with read replicas

MySQL

• Strengths: Performance, replication, wide ecosystem support
• Use Cases: Web applications, content management, e-commerce
• Scale: Horizontal scaling through sharding

NoSQL Databases

Document Stores (MongoDB, CouchDB)

• Strengths: Flexible schema, horizontal scaling, developer productivity
• Use Cases: Content management, catalogs, user profiles
• Trade-offs: Eventual consistency, complex queries limitations

Key-Value Stores (Redis, DynamoDB)

• Strengths: High performance, simple model, excellent caching
• Use Cases: Session storage, real-time analytics, gaming leaderboards
• Trade-offs: Limited query capabilities, data modeling constraints

Column-Family (Cassandra, HBase)

• Strengths: Write-heavy workloads, linear scalability, fault tolerance
• Use Cases: Time-series data, IoT applications, messaging systems
• Trade-offs: Query flexibility, consistency model complexity

Graph Databases (Neo4j, Amazon Neptune)

• Strengths: Relationship queries, pattern matching, recommendation engines
• Use Cases: Social networks, fraud detection, knowledge graphs
• Trade-offs: Specialized use cases, learning curve

NewSQL Databases

Distributed SQL (CockroachDB, TiDB, Spanner)

• Strengths: SQL compatibility with horizontal scaling
• Use Cases: Global applications requiring ACID guarantees
• Trade-offs: Complexity, latency for distributed transactions

Tools & Scripts

Schema Analyzer

• Input: SQL DDL files, JSON schema definitions
• Analysis: Normalization compliance, constraint validation, naming conventions
• Output: Analysis report, Mermaid ERD, improvement recommendations

Index Optimizer

• Input: Schema definition, query patterns
• Analysis: Missing indexes, redundancy detection, selectivity estimation
• Output: Index recommendations, CREATE INDEX statements, performance projections

Migration Generator

• Input: Current and target schemas
• Analysis: Schema differences, dependency resolution, risk assessment
• Output: Migration scripts, rollback plans, validation queries

Best Practices

Schema Design

1. Use meaningful names: Clear, consistent naming conventions
2. Choose appropriate data types: Right-sized columns for storage efficiency
3. Define proper constraints: Foreign keys, check constraints, unique indexes
4. Consider future growth: Plan for scale from the beginning
5. Document relationships: Clear foreign key relationships and business rules

Performance Optimization

1. Index strategically: Cover common query patterns without over-indexing
2. Monitor query performance: Regular analysis of slow queries
3. Partition large tables: Improve query performance and maintenance
4. Use appropriate isolation levels: Balance consistency with performance
5. Implement connection pooling: Efficient resource utilization

Security Considerations

1. Principle of least privilege: Grant minimal necessary permissions
2. Encrypt sensitive data: At rest and in transit
3. Audit access patterns: Monitor and log database access
4. Validate inputs: Prevent SQL injection attacks
5. Regular security updates: Keep database software current

Conclusion

Effective database design requires balancing multiple competing concerns: performance, scalability, maintainability, and business requirements. This skill provides the tools and knowledge to make informed decisions throughout the database lifecycle, from initial schema design through production optimization and evolution.

The included tools automate common analysis and optimization tasks, while the comprehensive guides provide the theoretical foundation for making sound architectural decisions. Whether building a new system or optimizing an existing one, these resources provide expert-level guidance for creating robust, scalable database solutions.