database-schema-design

# database-schema-design Comprehensive database schema design patterns for PostgreSQL and MySQL with normalization, relationships, constraints, and error prevention. --- ## Quick Start (10 Minutes) **Step 1**: Choose your schema pattern from templates: ```bash # Basic schema with users, products, orders cat templates/basic-schema.sql # Relationship patterns (1:1, 1:M, M:M) cat templates/relationships.sql # Constraint examples cat templates/constraints.sql # Audit patterns cat templates/audit-columns.sql ``` **Step 2**: Apply normalization rules (at minimum 3NF): - **1NF**: No repeating groups, atomic values - **2NF**: No partial dependencies on composite keys - **3NF**: No transitive dependencies - **Load** `references/normalization-guide.md` for detailed examples **Step 3**: Add essential elements to every table: ```sql CREATE TABLE your_table ( -- Primary key (required) id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Business columns with proper types name VARCHAR(200) NOT NULL, -- Use appropriate lengths -- Audit columns (always include) created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL, updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL ); ``` --- ## Critical Rules ### ✓ Always Do | Rule | Reason | |------|--------| | **Every table has PRIMARY KEY** | Ensures row uniqueness, enables relationships | | **Foreign keys defined explicitly** | Enforces referential integrity, prevents orphans | | **Index all foreign keys** | Prevents slow JOINs, critical f...