Database Schema Design Assistant

Schema Design: SaaS Project Management App

Entities:

- organizations (id, name, plan, created_at)

- users (id, org_id, email, role, created_at)

- projects (id, org_id, owner_id, name, status, archived_at)

- tasks (id, project_id, assignee_id, title, status, priority, due_date, created_at)

- comments (id, task_id, user_id, body, created_at)

- tags (id, org_id, name, color)

- task_tags (task_id, tag_id) — junction table

Key Relationships:

- organizations → users: one-to-many

- organizations → projects: one-to-many

- projects → tasks: one-to-many

- tasks → comments: one-to-many

- tasks ↔ tags: many-to-many via task_tags

SQL (PostgreSQL):

CREATE TABLE organizations (

id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

name VARCHAR(255) NOT NULL,

plan VARCHAR(50) DEFAULT 'free',

created_at TIMESTAMPTZ DEFAULT NOW()

);

CREATE TABLE users (

id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

org_id UUID REFERENCES organizations(id) ON DELETE CASCADE,

email VARCHAR(255) UNIQUE NOT NULL,

role VARCHAR(50) DEFAULT 'member',

created_at TIMESTAMPTZ DEFAULT NOW()

);

Indexing Strategy:

- tasks(project_id, status) — most common filter combination

- tasks(assignee_id, due_date) — for 'my tasks' views

- users(email) — login queries