Database Schema Skill

Design scalable database schemas with proper relationships, constraints, and indexing strategies.

When to Use

Use this skill when the user wants to:

• Design database tables and relationships
• Create schema migrations
• Define indexes and constraints
• Work with SQL or NoSQL databases
• Normalize or denormalize data models
• Design database migrations

Schema Design Principles

• Normalization: Balance between normalization and query performance
• Naming conventions: Use consistent, descriptive names
• Primary keys: Use natural or surrogate keys appropriately
• Foreign keys: Define relationships clearly
• Indexes: Index columns used in WHERE, JOIN, ORDER BY
• Constraints: Enforce data integrity with NOT NULL, UNIQUE, CHECK

Deliverables

• Complete database schema design
• Entity-relationship diagram (if applicable)
• Migration scripts
• Index strategy
• Data types and constraints
• Query examples

Database Types

• SQL: PostgreSQL, MySQL, SQLite, SQL Server
• NoSQL: MongoDB, PostgreSQL with JSONB, Redis
• Time-series: InfluxDB, TimescaleDB
• Search: Elasticsearch, PostgreSQL Full-Text Search

Quality Checklist

• Proper primary key design
• Clear foreign key relationships
• Appropriate indexes
• Data types are optimal
• Constraints prevent invalid data
• Migration scripts are reversible
• Documentation is complete