database-schema-evaluator

What this skill does

# Database Schema Evaluator

Comprehensive evaluation of database schema designs using expert panel analysis from multiple technical perspectives.

## When to Use

### Ideal Use Cases
- Reviewing schema designs before production deployment
- Comparing multiple schema approaches for a new system
- Assessing existing schema for refactoring needs
- Evaluating schema scalability for growth
- Identifying potential performance bottlenecks
- Checking compliance with normalization principles
- Reviewing data integrity and constraint design

### Anti-Patterns
- Trivial single-table designs
- Schema with no business context provided
- Purely academic exercises without real requirements
- Schemas already in production with extensive data

## Workflow

### Phase 1: Schema Analysis & Context Gathering

**Purpose:** Understand the schema structure, business requirements, and evaluation scope.

**Actions:**
1. Parse schema definition (DDL, ER diagram, or description)
2. Identify key entities, relationships, and constraints
3. Document business requirements and use cases
4. Note expected data volumes and access patterns
5. Identify specific evaluation concerns if provided

**Output Template:**
```yaml
schema_context:
  entities: [list of main tables/collections]
  relationships: [1:1, 1:N, N:M relationships]
  constraints: [PKs, FKs, unique, check constraints]
  indexes: [existing or proposed indexes]
  business_domain: [domain context]
  scale_expectations:
    initial_volume: [expected records]
    growth_rate: [expected growth]
    read_write_ratio: [expected ratio]
  specific_concerns: [any highlighted areas]
```

### Phase 2: Expert Panel Assembly

**Purpose:** Instantiate domain experts with relevant database perspectives.

**Expert Personas:**

1. **Data Architect**
   - Focus: Overall design patterns, normalization, data modeling best practices
   - Expertise: ER modeling, normalization forms (1NF-5NF, BCNF), denormalization tradeoffs
   - Evaluates: Structural integrity, design patterns, anti-patterns

2. **Performance Engineer**
   - Focus: Query optimization, indexing strategy, scalability
   - Expertise: Query execution plans, index design, partitioning, sharding
   - Evaluates: Access patterns, join complexity, index coverage, bottlenecks

3. **Data Integrity Guardian**
   - Focus: Constraints, validation rules, referential integrity
   - Expertise: ACID properties, constraint design, cascade rules, data quality
   - Evaluates: Constraint completeness, orphan prevention, data consistency

4. **Evolution Strategist**
   - Focus: Schema migration, backward compatibility, extensibility
   - Expertise: Schema versioning, migration patterns, API stability
   - Evaluates: Change flexibility, migration complexity, future-proofing

5. **Operations Specialist**
   - Focus: Backup/recovery, maintenance, monitoring
   - Expertise: Backup strategies, maintenance windows, operational complexity
   - Evaluates: Operational overhead, recovery scenarios, maintenance burden

### Phase 3: Multi-Lens Evaluation

**Purpose:** Each expert evaluates the schema from their specialized perspective.

**Evaluation Framework:**

```yaml
expert_evaluation:
  expert: [Expert Name]
  perspective: [Their focus area]
  
  strengths:
    - [Specific strength with rationale]
    - [Another strength with example]
  
  concerns:
    - issue: [Specific concern]
      severity: [critical|high|medium|low]
      rationale: [Why this matters]
      recommendation: [How to address]
  
  opportunities:
    - [Improvement opportunity]
    - [Optimization suggestion]
  
  risk_assessment:
    - risk: [Potential future problem]
      likelihood: [high|medium|low]
      impact: [high|medium|low]
      mitigation: [Suggested approach]
  
  score: [0-10 from this perspective]
  confidence: [0-1 confidence in assessment]
```

**Evaluation Criteria by Expert:**

| Expert | Primary Criteria | Secondary Criteria |
|--------|-----------------|-------------------|
| Data Architect | Normalization level, Design patterns | Naming conventions, Documentation |
| Performance Engineer | Index efficiency, Query complexity | Join paths, Denormalization benefits |
| Data Integrity Guardian | Constraint coverage, Referential integrity | Validation rules, Orphan prevention |
| Evolution Strategist | Migration simplicity, Extensibility | Backward compatibility, Version strategy |
| Operations Specialist | Backup feasibility, Maintenance overhead | Monitoring capability, Recovery time |

### Phase 4: Cross-Expert Deliberation

**Purpose:** Synthesize perspectives and identify consensus/conflicts.

**Deliberation Process:**
1. Identify areas of expert agreement (reinforced findings)
2. Surface conflicting assessments (tradeoff points)
3. Evaluate interdependencies between concerns
4. Prioritize issues based on business context
5. Generate unified recommendations

**Conflict Resolution Matrix:**
```yaml
conflicts:
  - conflict: [Description of disagreement]
    expert_1: [Position and rationale]
    expert_2: [Alternative position]
    resolution: [Recommended approach considering tradeoffs]
    business_impact: [What this means for the system]
```

### Phase 5: Comprehensive Scoring

**Purpose:** Generate quantitative assessment across dimensions.

**Scoring Dimensions:**

| Dimension | Weight | Factors |
|-----------|--------|---------|
| Correctness | 25% | Normalization, integrity, consistency |
| Performance | 20% | Query efficiency, scalability potential |
| Maintainability | 20% | Clarity, documentation, operational simplicity |
| Flexibility | 15% | Extensibility, migration paths |
| Robustness | 10% | Error handling, constraint coverage |
| Security | 10% | Access control, audit capability |

**Scoring Algorithm:**
```
dimension_score = Σ(expert_score × expert_weight) / Σ(expert_weights)
overall_score = Σ(dimension_score × dimension_weight)
confidence = min(expert_confidences) × consensus_factor
```

### Phase 6: Final Report Generation

**Purpose:** Deliver actionable evaluation with clear recommendations.

## Output Format

```markdown
# Database Schema Evaluation Report

## Executive Summary
- **Overall Score:** [X/10]
- **Confidence:** [X%]
- **Recommendation:** [APPROVE|APPROVE_WITH_CONDITIONS|REVISE|REJECT]
- **Key Strengths:** [Top 3 strengths]
- **Critical Issues:** [Top 3 concerns if any]

## Schema Overview
[Brief description of schema purpose and structure]

## Expert Evaluations

### Data Architecture Assessment
[Data Architect findings]
- **Score:** X/10
- **Key Findings:** [Bullets]

### Performance Analysis
[Performance Engineer findings]
- **Score:** X/10
- **Key Findings:** [Bullets]

### Data Integrity Review
[Data Integrity Guardian findings]
- **Score:** X/10
- **Key Findings:** [Bullets]

### Evolution Capability
[Evolution Strategist findings]
- **Score:** X/10
- **Key Findings:** [Bullets]

### Operational Assessment
[Operations Specialist findings]
- **Score:** X/10
- **Key Findings:** [Bullets]

## Consolidated Findings

### Strengths
1. [Major strength with supporting expert consensus]
2. [Another strength]

### Critical Issues
1. **[Issue Name]**
   - Severity: [Critical/High/Medium/Low]
   - Impact: [Description]
   - Recommendation: [Specific action]

### Improvement Opportunities
1. [Opportunity with expected benefit]
2. [Another opportunity]

## Tradeoff Analysis
[Discussion of key design tradeoffs and recommendations]

## Risk Assessment

| Risk | Likelihood | Impact | Mitigation Strategy |
|------|------------|--------|-------------------|
| [Risk 1] | High/Medium/Low | High/Medium/Low | [Strategy] |

## Recommendations

### Immediate Actions
1. [Required change before deployment]
2. [Another critical change]

### Short-term Improvements (1-3 months)
1. [Important but not blocking]

### Long-term Considerations (3+ months)
1. [Future optimization]

## Detailed Scoring Matrix

| Dimension | Score | Weight | Weighted Score | Notes |
|-----------|-------|--------|---------------|--