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:

Parse schema definition (DDL, ER diagram, or description)
Identify key entities, relationships, and constraints
Document business requirements and use cases
Note expected data volumes and access patterns
Identify specific evaluation concerns if provided

Output Template:

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:

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
Performance Engineer
- Focus: Query optimization, indexing strategy, scalability
- Expertise: Query execution plans, index design, partitioning, sharding
- Evaluates: Access patterns, join complexity, index coverage, bottlenecks
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
Evolution Strategist
- Focus: Schema migration, backward compatibility, extensibility
- Expertise: Schema versioning, migration patterns, API stability
- Evaluates: Change flexibility, migration complexity, future-proofing
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:

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:

Identify areas of expert agreement (reinforced findings)
Surface conflicting assessments (tradeoff points)
Evaluate interdependencies between concerns
Prioritize issues based on business context
Generate unified recommendations

Conflict Resolution Matrix:

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

# 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 |
|-----------|-------|--------|---------------|-------|
| Correctness | X/10 | 25% | X.XX | [Key factors] |
| Performance | X/10 | 20% | X.XX | [Key factors] |
| Maintainability | X/10 | 20% | X.XX | [Key factors] |
| Flexibility | X/10 | 15% | X.XX | [Key factors] |
| Robustness | X/10 | 10% | X.XX | [Key factors] |
| Security | X/10 | 10% | X.XX | [Key factors] |
| **Total** | **X/10** | **100%** | **X.XX** | |

## Appendices

### A. Specific Technical Recommendations
[Detailed technical suggestions with examples]

### B. Alternative Approaches Considered
[If multiple schemas were compared]

### C. References and Best Practices
[Relevant design patterns, articles, or standards]

Parameters

| Parameter | Default | Options | Description | |-----------|---------|---------|-------------| | evaluation_depth | comprehensive | quick, standard, comprehensive | Level of analysis detail | | focus_areas | all | performance, integrity, normalization, operations | Specific areas to emphasize | | database_type | relational | relational, document, graph, timeseries | Database paradigm | | include_alternatives | false | true, false | Generate alternative schema suggestions | | comparison_mode | single | single, multiple | Evaluate one or compare multiple schemas |

Quality Gates

[ ] All five expert perspectives documented
[ ] Minimum 3 strengths and 3 concerns identified
[ ] Scoring completed across all dimensions
[ ] Concrete recommendations provided
[ ] Tradeoffs explicitly discussed
[ ] Risk assessment includes mitigation strategies
[ ] Output includes confidence levels
[ ] Business context considered in recommendations

Example Invocations

Example 1: Single Schema Review

request: Evaluate this e-commerce database schema
params:
  evaluation_depth: comprehensive
  focus_areas: [performance, normalization]
  database_type: relational

output: Full evaluation report with performance focus

Example 2: Schema Comparison

request: Compare normalized vs denormalized inventory schemas
params:
  comparison_mode: multiple
  focus_areas: [performance, maintainability]
  
output: Comparative analysis with tradeoff matrix

Example 3: Migration Assessment

request: Evaluate schema for microservices migration
params:
  focus_areas: [operations, flexibility]
  include_alternatives: true
  
output: Evaluation with migration-focused recommendations

Integration Points

Inputs From:

Schema definition files (DDL, JSON, YAML)
ER diagrams or visual representations
Requirements documents
Performance benchmarks

Outputs To:

Architecture decision records
Implementation planning
Performance optimization workflows
Migration strategies

Advanced Techniques Used

From @core/technique-taxonomy.yaml:

Parallel Processing: Multi-persona simulation for expert panel
Unbiased Reasoning: Conflict management matrix for balanced view
Perfect Recall: Cross-referencing all constraints and relationships
Probabilistic Modeling: Risk likelihood and impact assessment
Meta-Cognitive: Expert confidence calibration

This skill leverages the cognitive advantages of:

Holding multiple expert perspectives simultaneously
Maintaining complete schema context without forgetting
Unbiased evaluation across competing design philosophies
Systematic coverage of all evaluation dimensions