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
→ 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.

---

## 中文说明

# Database Designer - POWERFUL 层级技能

## 概述

一项全面的数据库设计技能，为现代数据库系统提供专家级的分析、优化和迁移能力。该技能将理论原则与实用工具相结合，帮助架构师和开发者创建可扩展、高性能且易于维护的数据库模式。

## 核心能力

### 模式设计与分析
- **范式化分析**：自动检测范式化级别（1NF 到 BCNF）
- **反范式化策略**：为性能优化提供智能建议
- **数据类型优化**：识别不合适的类型和大小问题
- **约束分析**：缺失的外键、唯一约束和空值检查
- **命名约定校验**：一致的表和列命名模式
- **ERD 生成**：从 DDL 自动创建 Mermaid 图

### 索引优化
- **索引缺口分析**：识别外键和查询模式上缺失的索引
- **复合索引策略**：为多列索引确定最优的列顺序
- **索引冗余检测**：消除重叠和未使用的索引
- **性能影响建模**：选择性估算和查询成本分析
- **索引类型选择**：B-tree、hash、partial、covering 和专用索引

### 迁移管理
- **零停机迁移**：expand-contract 模式的实现
- **模式演进**：安全地添加、删除列以及更改类型
- **数据迁移脚本**：自动化的数据转换和校验
- **回滚策略**：具备校验能力的完整回退
- **执行规划**：经依赖关系解析后有序排列的迁移步骤

## 数据库设计原则
→ 详情参见 references/database-design-reference.md

## 最佳实践

### 模式设计
1. **使用有意义的名称**：清晰、一致的命名约定
2. **选择合适的数据类型**：为存储效率而合理调整列大小
3. **定义恰当的约束**：外键、检查约束、唯一索引
4. **考虑未来增长**：从一开始就为扩展做规划
5. **记录关系**：清晰的外键关系和业务规则

### 性能优化
1. **有策略地建立索引**：覆盖常见查询模式而不过度索引
2. **监控查询性能**：定期分析慢查询
3. **对大表进行分区**：改善查询性能和维护
4. **使用合适的隔离级别**：在一致性与性能之间取得平衡
5. **实现连接池**：高效利用资源

### 安全考量
1. **最小权限原则**：仅授予必要的最小权限
2. **加密敏感数据**：在静态存储和传输过程中均加密
3. **审计访问模式**：监控并记录数据库访问
4. **校验输入**：防止 SQL 注入攻击
5. **定期安全更新**：保持数据库软件为最新版本

## 结论

有效的数据库设计需要在多个相互竞争的关注点之间取得平衡：性能、可扩展性、可维护性和业务需求。该技能提供了工具和知识，帮助在整个数据库生命周期中做出明智决策——从初始模式设计到生产环境优化与演进。

所包含的工具可自动化常见的分析和优化任务，而全面的指南则为做出合理的架构决策提供理论基础。无论是构建新系统还是优化现有系统，这些资源都为创建健壮、可扩展的数据库解决方案提供专家级的指导。