Database Designer

---

name: "database-designer" description: "Database Designer - POWERFUL Tier Skill"

---

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

→ 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

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.

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