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

# Database Designer - POWERFUL Tier Skill

A comprehensive database design and analysis toolkit that provides expert-level schema analysis, index optimization, and migration generation capabilities for modern database systems.

## Features

### 🔍 Schema Analyzer
- **Normalization Analysis**: Automated detection of 1NF through BCNF violations
- **Data Type Optimization**: Identifies antipatterns and inappropriate types
- **Constraint Analysis**: Finds missing foreign keys, unique constraints, and checks
- **ERD Generation**: Creates Mermaid diagrams from DDL or JSON schema
- **Naming Convention Validation**: Ensures consistent naming patterns

### ⚡ Index Optimizer  
- **Missing Index Detection**: Identifies indexes needed for query patterns
- **Composite Index Design**: Optimizes column ordering for maximum efficiency
- **Redundancy Analysis**: Finds duplicate and overlapping indexes
- **Performance Modeling**: Estimates selectivity and query performance impact
- **Covering Index Recommendations**: Eliminates table lookups

### 🚀 Migration Generator
- **Zero-Downtime Migrations**: Implements expand-contract patterns
- **Schema Evolution**: Handles column changes, table renames, constraint updates
- **Data Migration Scripts**: Automated data transformation and validation
- **Rollback Planning**: Complete reversal capabilities for all changes
- **Execution Orchestration**: Dependency-aware migration ordering

## Quick Start

### Prerequisites
- Python 3.7+ (no external dependencies required)
- Database schema in SQL DDL format or JSON
- Query patterns (for index optimization)

### Installation
```bash
# Clone or download the database-designer skill
cd engineering/database-designer/

# Make scripts executable
chmod +x *.py
```

## Usage Examples

### Schema Analysis

**Analyze SQL DDL file:**
```bash
python schema_analyzer.py --input assets/sample_schema.sql --output-format text
```

**Generate ERD diagram:**
```bash
python schema_analyzer.py --input assets/sample_s...