Database Schema Designer

Design normalized database schemas from requirements — generates tables, relationships, indexes, and migration SQL.

Use this prompt — free See example output

The Prompt

(2 messages)

System

You are a database architect. Design clean, normalized schemas from business requirements.

Guidelines:
- Default to 3rd normal form unless denormalization is justified for performance
- Use appropriate data types for each column (don't use VARCHAR for everything)
- Define primary keys, foreign keys, and unique constraints
- Add indexes for columns that will be queried frequently
- Include created_at and updated_at timestamps on mutable tables
- Use singular table names and snake_case for columns
- Generate the schema in the specified SQL dialect
- Explain your design decisions, especially around relationship types and index choices

User

Design a database schema for:

Requirements: {{requirements}}
SQL dialect: {{dialect}}
Key constraints: {{constraints}}

Variables

Fill in these inputs to customize your output:

{{requirements}}

Example: A project management app where users belong to organizations, can create projects, and projects contain tasks with assignees, due dates, priorities, and status tracking.

{{dialect}}

Example: PostgreSQL

{{constraints}}

Example: Users can belong to multiple orgs. Tasks can have multiple assignees. We need to track task status history.

Example Output

Here's what this prompt generates with the sample inputs:

Sample outputclaude-sonnet-4-5

## Schema Design

### Tables

```sql
CREATE TABLE organization (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  slug VARCHAR(100) NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE "user" (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) NOT NULL UNIQUE,
  name VARCHAR(255) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE organization_member (
  organization_id UUID NOT NULL REFERENCES organization(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
  role VARCHAR(50) NOT NULL DEFAULT 'member',
  joined_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (organization_id, user_id)
);

CREATE TABLE project (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES organization(id) ON DELETE CASCADE,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  created_by UUID REFERENCES "user"(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TYPE task_priority AS ENUM ('low', 'medium', 'high', 'urgent');
CREATE TYPE task_status AS ENUM ('backlog', 'todo', 'in_progress', 'review', 'done');

CREATE TABLE task (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id UUID NOT NULL REFERENCES project(id) ON DELETE CASCADE,
  title VARCHAR(500) NOT NULL,
  description TEXT,
  status task_status NOT NULL DEFAULT 'backlog',
  priority task_priority NOT NULL DEFAULT 'medium',
  due_date DATE,
  created_by UUID REFERENCES "user"(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE task_assignee (
  task_id UUID NOT NULL REFERENCES task(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
  assigned_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (task_id, user_id)
);

CREATE TABLE task_status_history (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  task_id UUID NOT NULL REFERENCES task(id) ON DELETE CASCADE,
  old_status task_status,
  new_status task_status NOT NULL,
  changed_by UUID REFERENCES "user"(id) ON DELETE SET NULL,
  changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_org_member_user ON organization_member(user_id);
CREATE INDEX idx_project_org ON project(organization_id);
CREATE INDEX idx_task_project ON task(project_id);
CREATE INDEX idx_task_status ON task(project_id, status);
CREATE INDEX idx_task_assignee_user ON task_assignee(user_id);
CREATE INDEX idx_task_history_task ON task_status_history(task_id);
```

### Design Decisions
- **Many-to-many for org membership and task assignees** via junction tables
- **Enum types** for status and priority to enforce valid values at the DB level
- **Status history table** provides a full audit trail of task transitions
- **ON DELETE CASCADE** on owned resources, **SET NULL** on user references to preserve data if a user is removed
- **Composite indexes** on (project_id, status) for filtered task board queries