Design robust, scalable database schemas for SQL and NoSQL databases. Provides normalization guidelines, indexing strategies, migration patterns, constraint design, and performance optimization. Ensures data integrity, query performance, and maintainable data models.

Database Schema Designer Design production ready database schemas with best practices built in. Quick Start Just describe your data model: You'll get a complete SQL schema like: What to include in your request: Entities (users, products, orders) Key relationships (users have orders, orders have items) Scale hints (high traffic, millions of records) Database preference (SQL/NoSQL) defaults to SQL if not specified Triggers 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" Key Terms Term Definition Normalization Organizing data to reduce redundancy (1NF → 2NF → 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 Quick Reference Task Approach Key Consideration New schema Normalize to 3NF first Domain modeling over UI SQL vs NoSQL Access patterns decide Read/write ratio matters Primary keys INT or UUID UUID for distributed systems Foreign keys Always constrain ON DELETE strategy critical Indexes FKs + WHERE columns Column order matters Migrations Always reversible Backward compatible first Process Overview Commands Command When to Use Action design schema for {domain} Starting fresh Full schema generation normalize {table} Fixing existing table Apply normalization rules add indexes for {table} Performance issues Generate index strategy migration for {change} Schema evolution Create reversible migration review schema Code review Audit existing schema Workflow: Start with design schema → iterate with normalize → optimize with add indexes → evolve with migration Core Principles Principle WHY Implementation Model the Domain UI changes, domain doesn't Entity names reflect business concepts Data Integrity First Corruption is costly to fix Constraints at database level Optimize for Access Pattern Can't optimize for both OLTP: normalized, OLAP: denormalized Plan for Scale Retrofitting is painful Index strategy + partitioning plan Anti Patterns Avoid Why Instead 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 Can't compare/sort DATE, TIMESTAMP types SELECT in queries Fetches unnecessary data Explicit column lists Non reversible migrations Can't rollback Always write DOWN migration Adding NOT NULL without default Breaks existing rows Add nullable, backfill, then constrain Verification Checklist After designing a schema: [ ] 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 <details <summary <strong Deep Dive: Normalization (SQL)</strong </summary Normal Forms Form Rule Violation Example 1NF Atomic values, no repeating groups product ids = '1,2,3' 2NF 1NF + no partial dependencies customer name in order items 3NF 2NF + no transitive dependencies country derived from postal code 1st Normal Form (1NF) 2nd Normal Form (2NF) 3rd Normal Form (3NF) When to Denormalize Scenario Denormalization Strategy Read heavy reporting Pre calculated aggregates Expensive JOINs Cached derived columns Analytics dashboards Materialized views </details <details <summary <strong Deep Dive: Data Types</strong </summary String Types Type Use Case Example CHAR(n) Fixed length State codes, ISO dates VARCHAR(n) Variable length Names, emails TEXT Long content Articles, descriptions Numeric Types Type Range Use Case TINYINT 128 to 127 Age, status codes SMALLINT 32K to 32K Quantities INT 2.1B to 2.1B IDs, counts BIGINT Very large Large IDs, timestamps DECIMAL(p,s) Exact precision Money FLOAT/DOUBLE Approximate Scientific data Date/Time Types Boolean </details <details <summary <strong Deep Dive: Indexing Strategy</strong </summary When to Create Indexes Always Index Reason Foreign keys Speed up JOINs WHERE clause columns Speed up filtering ORDER BY columns Speed up sorting Unique constraints Enforced uniqueness Index Types Type Best For Example B Tree Ranges, equality price 100 Hash Exact matches only email = 'x@y.com' Full text Text search MATCH AGAINST Partial Subset of rows WHERE is active = true Composite Index Order Rule: Most selective column first, or column most queried alone. Index Pitfalls Pitfall Problem Solution Over indexing Slow writes Only index what's queried Wrong column order Unused index Match query patterns Missing FK indexes Slow JOINs Always index FKs </details <details <summary <strong Deep Dive: Constraints</strong </summary Primary Keys Foreign Keys Strategy Use When CASCADE Dependent data (order items) RESTRICT Important references (prevent accidents) SET NULL Optional relationships Other Constraints </details <details <summary <strong Deep Dive: Relationship Patterns</strong </summary One to Many Many to Many Self Referencing Polymorphic </details <details <summary <strong Deep Dive: NoSQL Design (MongoDB)</strong </summary Embedding vs Referencing Factor Embed Reference Access pattern Read together Read separately Relationship 1:few 1:many Document size Small Approaching 16MB Update frequency Rarely Frequently Embedded Document Referenced Document MongoDB Indexes </details <details <summary <strong Deep Dive: Migrations</strong </summary Migration Best Practices Practice WHY Always reversible Need to rollback Backward compatible Zero downtime deploys Schema before data Separate concerns Test on staging Catch issues early Adding a Column (Zero Downtime) Renaming a Column (Zero Downtime) Migration Template </details <details <summary <strong Deep Dive: Performance Optimization</strong </summary Query Analysis Look For Meaning type: ALL Full table scan (bad) type: ref Index used (good) key: NULL No index used rows: high Many rows scanned N+1 Query Problem Optimization Techniques Technique When to Use Add indexes Slow WHERE/ORDER BY Denormalize Expensive JOINs Pagination Large result sets Caching Repeated queries Read replicas Read heavy load Partitioning Very large tables </details Extension Points 1. Database Specific Patterns: Add MySQL vs PostgreSQL vs SQLite variations 2. Advanced Patterns: Time series, event sourcing, CQRS, multi tenancy 3. ORM Integration: TypeORM, Prisma, SQLAlchemy patterns 4. Monitoring: Query performance tracking, slow query alerts

database-schema-designer