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

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

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

Key Terminology

License

MIT