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