database-design

Database Design

Design a database schema from requirements.

Workflow

1. Identify Entities

From the requirements, extract the core entities (nouns):

• Users, Teams, Projects, Tasks, Comments, etc.
• Each entity becomes a table

2. Define Relationships

Relationship	Implementation
One-to-one	Foreign key with unique constraint, or embed in same table
One-to-many	Foreign key on the "many" side
Many-to-many	Junction/join table
Self-referential	Foreign key pointing to same table (e.g. parent_id)

3. Design the Schema

For each table:

CREATE TABLE users (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email       TEXT NOT NULL UNIQUE,
  name        TEXT NOT NULL,
  avatar_url  TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE projects (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name        TEXT NOT NULL,
  owner_id    UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

4. Apply Best Practices

Primary keys:

• Use UUID for distributed systems or public-facing IDs
• Use SERIAL/BIGSERIAL for internal-only IDs (faster joins)

Timestamps:

• Always add created_at and updated_at
• Use TIMESTAMPTZ (with timezone), never TIMESTAMP

Naming:

• Tables: plural snake_case (users, project_members)
• Columns: singular snake_case (user_id, created_at)
• Indexes: idx_<table>_<columns> (idx_users_email)

Constraints:

• NOT NULL on everything unless it's genuinely optional
• UNIQUE on natural keys (email, slug, external IDs)
• REFERENCES with ON DELETE behavior (CASCADE, SET NULL, RESTRICT)
• CHECK constraints for enums or value ranges

5. Add Indexes

-- For columns you filter/sort by frequently
CREATE INDEX idx_projects_owner_id ON projects(owner_id);

-- For unique lookups
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Composite for common query patterns
CREATE INDEX idx_tasks_project_status ON tasks(project_id, status);