Database Schema Designer

# 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: ```sql 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