database-schema-designer

A comprehensive skill for designing production-ready database schemas with built-in best practices for both SQL and NoSQL databases.

Purpose

The Database Schema Designer skill helps you create robust, scalable database schemas by providing:

• Normalization guidance - Apply proper normal forms (1NF, 2NF, 3NF) to eliminate data redundancy
• Indexing strategies - Optimize query performance with the right indexes
• Migration patterns - Evolve schemas safely with reversible, zero-downtime migrations
• Constraint design - Ensure data integrity with proper foreign keys, checks, and unique constraints
• Performance optimization - Design for your specific access patterns (OLTP vs OLAP)

Whether you are starting a new project or evolving an existing database, this skill ensures your schema follows industry best practices and avoids common pitfalls.

When to Use

Use this skill when you need to:

• Design a new database schema from scratch
• Normalize an existing table structure
• Add indexes to improve query performance
• Create migration scripts for schema changes
• Review and audit existing schemas
• Choose between SQL and NoSQL approaches

Trigger Phrases

| Trigger | Example | |---------|---------| | design schema | "design a schema for user authentication" | | database design | "database design for multi-tenant SaaS" | | create tables | "create tables for a blog system" | | schema for | "schema for inventory management" | | model data | "model data for real-time analytics" | | I need a database | "I need a database for tracking orders" | | design NoSQL | "design NoSQL schema for product catalog" |

How It Works

The skill follows a four-phase process:

Phase 1: Analysis

• Identify entities and their relationships
• Determine access patterns (read-heavy vs write-heavy)
• Choose SQL or NoSQL based on requirements

Phase 2: Design

• Normalize to 3NF for SQL or determine embed/reference strategy for NoSQL
• Define primary keys and foreign keys
• Choose appropriate data types
• Add constraints (UNIQUE, CHECK, NOT NULL)

Phase 3: Optimize

• Plan indexing strategy based on query patterns
• Consider denormalization for read-heavy queries
• Add audit timestamps (created_at, updated_at)

Phase 4: Migrate

• Generate reversible migration scripts (up + down)
• Ensure backward compatibility
• Plan for zero-downtime deployment

Key Features

SQL Schema Design

• Normalization - Automatic application of 1NF, 2NF, and 3NF rules
• Data Types - Appropriate type selection (DECIMAL for money, proper VARCHAR sizing)
• Constraints - Foreign keys with ON DELETE strategies, CHECK constraints, UNIQUE constraints
• Indexes - B-Tree, Hash, Full-text, and Partial index recommendations

NoSQL Schema Design (MongoDB)

• Embedding vs Referencing - Guidance on when to embed documents vs use references
• Index Strategies - Single field, composite, text search, and geospatial indexes
• Document Structure - Optimal document design based on access patterns

Relationship Patterns

• One-to-Many relationships
• Many-to-Many with junction tables
• Self-referencing hierarchies
• Polymorphic associations

Migration Support

• Zero-downtime migration patterns
• Reversible migration templates
• Safe column addition/rename strategies
• Backward compatible changes

Usage Examples

Basic Schema Design

design a schema for an e-commerce platform with users, products, orders

Output:

CREATE TABLE users (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id),
  total DECIMAL(10,2) NOT NULL,
  INDEX idx_orders_user (user_id)
);

Available Commands

| Command | Purpose | |---------|---------| | design schema for {domain} | Generate a complete schema from scratch | | normalize {table} | Apply normalization rules to fix an existing table | | add indexes for {table} | Generate an index strategy for performance | | migration for {change} | Create reversible migration scripts | | review schema | Audit an existing schema for issues |

Request Tips

Include these details in your request for best results:

• Entities - users, products, orders
• Key relationships - users have orders, orders have items
• Scale hints - high-traffic, millions of records
• Database preference - SQL or NoSQL (defaults to SQL if not specified)
• Access patterns - read-heavy analytics, write-heavy transactions

Prerequisites

No special tools or dependencies required. The skill generates standard SQL or NoSQL schema definitions that work with:

• MySQL / MariaDB
• PostgreSQL
• SQLite
• MongoDB
• And other compatible databases

Output

The skill produces:

1. Schema DDL - Complete CREATE TABLE statements with all constraints
2. Index Definitions - Optimized indexes for your query patterns
3. Migration Scripts - Reversible UP and DOWN migrations
4. Mermaid Diagrams - Entity-relationship diagrams (when requested)
5. Verification Checklist - Items to review before deploying

Verification Checklist

After designing a schema, verify:

• [ ] Every table has a primary key
• [ ] All relationships have foreign key constraints
• [ ] ON DELETE strategy defined for each FK
• [ ] Indexes exist on all foreign keys
• [ ] Indexes exist on frequently queried columns
• [ ] Appropriate data types (DECIMAL for money, etc.)
• [ ] NOT NULL on required fields
• [ ] UNIQUE constraints where needed
• [ ] CHECK constraints for validation
• [ ] created_at and updated_at timestamps
• [ ] Migration scripts are reversible
• [ ] Tested on staging with production data

Best Practices

Do

• Start with domain modeling, not UI requirements
• Normalize to 3NF first, then selectively denormalize
• Use DECIMAL for money (never FLOAT)
• Always define foreign key constraints
• Index every foreign key column
• Size VARCHAR columns appropriately
• Store dates in DATE/TIMESTAMP types
• Always write reversible migrations
• Test migrations on staging with production-like data

Avoid

| Anti-Pattern | Problem | Solution | |--------------|---------|----------| | VARCHAR(255) everywhere | Wastes storage, hides intent | Size appropriately per field | | FLOAT for money | Rounding errors | DECIMAL(10,2) | | Missing FK constraints | Orphaned data | Always define foreign keys | | No indexes on FKs | Slow JOINs | Index every foreign key | | Storing dates as strings | Cannot compare/sort properly | Use DATE/TIMESTAMP types | | Non-reversible migrations | Cannot rollback safely | Always write DOWN migration |

Key Terminology

| Term | Definition | |------|------------| | Normalization | Organizing data to reduce redundancy (1NF to 2NF to 3NF) | | 3NF | Third Normal Form - no transitive dependencies between columns | | OLTP | Online Transaction Processing - write-heavy, needs normalization | | OLAP | Online Analytical Processing - read-heavy, benefits from denormalization | | Foreign Key (FK) | Column that references another table's primary key | | Index | Data structure that speeds up queries (at cost of slower writes) | | Access Pattern | How your app reads/writes data (queries, joins, filters) | | Denormalization | Intentionally duplicating data to speed up reads |

License

MIT