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:

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 DEFAULT 'USD',
    shipping_address_id UUID REFERENCES addresses(id),
    notes TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE orders IS 'Customer purchase orders';
COMMENT ON COLUMN orders.total_cents IS 'Order total in smallest currency unit';

Conventions:

• Use UUID for primary keys (or BIGSERIAL if performance-critical)
• Store money as integers (cents) to avoid floating point issues
• Use TIMESTAMPTZ (not TIMESTAMP) for all time columns
• Add CHECK constraints for enum-like values
• Include created_at and updated_at on every table
• Use ON DELETE CASCADE or SET NULL explicitly