database-schema-design

# Database Schema Design ## Instructions ### Step 1: Gather Requirements Ask: 1. What entities need to be stored? (e.g., users, orders, products) 2. What are the relationships? (one-to-one, one-to-many, many-to-many) 3. What queries will be most common? (affects indexing decisions) 4. Expected scale? (thousands vs millions of rows) 5. Database engine? (PostgreSQL, MySQL, SQLite) 6. Migration tool? (raw SQL, Flyway, Liquibase, Prisma, Alembic) ### Step 2: Design Normalized Schema Apply normalization rules: **First Normal Form (1NF):** - Eliminate repeating groups - Each column holds atomic values - Each row is unique (has primary key) **Second Normal Form (2NF):** - Meet 1NF - Remove partial dependencies on composite keys **Third Normal Form (3NF):** - Meet 2NF - Remove transitive dependencies **When to denormalize:** - Read-heavy workloads with expensive joins - Reporting tables (materialize aggregations) - Caching frequently computed values with clear update triggers Always start normalized, then denormalize with documented justification. ### Step 3: Define Tables Use this format for each table: ```sql CREATE TABLE orders ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE, status VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled')), total_cents BIGINT NOT NULL CHECK (total_cents >= 0), currency CHAR(3) NOT NULL ...