/database-designer

name: "database-designer"
description: "Use when the user asks to design database schemas, plan data migrations, optimize queries, choose between SQL and NoSQL, or model data relationships."

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

Tool Workflow (run these — do not analyze schemas by hand)

All paths relative to this skill folder; sample inputs in `assets/`.

1. Analyze the schema

python3 schema_analyzer.py --input schema.sql --generate-erd --output-format json -o analysis.json

Accepts SQL DDL or JSON schema (`assets/sample_schema.sql` / `sample_schema.json`). Output includes normalization findings, missing constraints, naming issues, and a Mermaid ERD — show the ERD to the user and fix flagged issues before optimizing.

2. Optimize indexes against real query patterns

python3 index_optimizer.py --schema assets/sample_schema.json --queries assets/sample_query_patterns.json --analyze-existing --format json -o indexes.json

Write the user's hot queries into a query-patterns JSON first (copy `assets/sample_query_patterns.json`). Output is a priority-ordered list of CREATE INDEX recommendations plus redundant-index removals.

3. Generate the migration

python3 migration_generator.py --current current_schema.json --target target_schema.json --zero-downtime --format sql -o migration.sql

`--zero-downtime` emits an expand-contract plan; `--validate-only` checks feasibility without generating SQL.

4. Verification loop

Re-run step 1 on the *target* schema and assert the issues found in the first pass are gone; run `migration_generator.py --validate-only` before handing over the migration.

Database Design Principles

→ See references/database-design-reference.md for details

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

Query Generation Patterns

SELECT with JOINs

-- INNER JOIN: only matching rows
SELECT o.id, c.name, o.total
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id;

-- LEFT JOIN: all left rows, NULLs for non-matches
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

-- Self-join: hierarchical data (employees/managers)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

Common Table Expressions (CTEs)

-- Recursive CTE for org chart
WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.depth + 1
  FROM employees e INNER JOIN org o ON o.id = e.manager_id
)
SELECT * FROM org ORDER BY depth, name;

Window Functions

-- ROW_NUMBER for pagination / dedup
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
FROM orders;

-- RANK with gaps, DENSE_RANK without gaps
SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank FROM leaderboard;

-- LAG/LEAD for comparing adjacent rows
SELECT date, revenue,
  revenue - LAG(revenue) OVER (ORDER BY date) AS daily_change
FROM daily_sales;

Aggregation Patterns

-- FILTER clause (PostgreSQL) for condi