Project Management ER Diagram

Project management tools are built on projects, the members allowed in them, and tasks that can be grouped into sprints and tagged with labels. Membership is a many-to-many with a role; labels attach to tasks through a second join table.

Tenancy is a three-level chain: organizations own projects, projects own everything else, and project_members controls who sees what. The membership join table carries role, so admin-of-project-A and member-of-project-B is two rows rather than a column on users. Labels are scoped to project_id — unlike a global tag list, each project defines its own vocabulary with its own colors, which keeps a red urgent label in one project from meaning anything in another. task_labels is the join that attaches them to tasks.

tasks.sprint_id is nullable, and that null is the backlog: a task belongs to a project from the start but only joins a sprint when planning assigns it. assignee_id is nullable for the same reason — unassigned work exists. Sprints are optional per project, since a maintenance project may never use them, while comments anchor to tasks so discussion follows the work item. story_points and due_date are both nullable because neither convention applies to every team; the schema stays permissive where practices vary.

Cascade rules follow ownership: deleting an organization wipes projects, members, sprints, tasks, labels and comments in one go, and deleting a task removes its label join rows and comments. The edges without rules are the ones that would destroy live data: tasks.sprint_id has no ON DELETE, so a sprint with tasks cannot be deleted until they are moved or unsprinted. users referenced by assignee_id and comments.author_id are equally protected — offboarding a teammate means unassigning and reassigning, not deleting the row.

The first extension is task_history (task_id, field, old_value, new_value, changed_by, changed_at): status transitions and reassignments are exactly what managers ask about later, and none of it survives in the current columns. Epics come next — either a parent_task_id self-reference on tasks or a separate epics table, depending on whether an epic is a large task or its own entity. A task_watchers join table, mirroring task_labels, covers notification subscriptions, which this schema currently has no place to express.

The diagram

Tables (9)

Relationships (11)

• 1:Norganizations.id→projects.organization_idON DELETE CASCADE
• 1:1projects.id→project_members.project_idON DELETE CASCADE
• 1:1users.id→project_members.user_idON DELETE CASCADE
• 1:Nprojects.id→sprints.project_idON DELETE CASCADE
• 1:Nprojects.id→tasks.project_idON DELETE CASCADE
• 1:Nsprints.id→tasks.sprint_id
• 1:Nusers.id→tasks.assignee_id
• 1:Nprojects.id→labels.project_idON DELETE CASCADE
• 1:Ntasks.id→comments.task_idON DELETE CASCADE
• 1:Nusers.id→comments.author_id
• N:Mtasks.id→labels.id

The SQL

CREATE TABLE organizations (
  id SERIAL PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  full_name VARCHAR(120) NOT NULL
);

CREATE TABLE projects (
  id SERIAL PRIMARY KEY,
  organization_id INTEGER NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  name VARCHAR(120) NOT NULL,
  description TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE project_members (
  project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role VARCHAR(20) NOT NULL DEFAULT 'member',
  PRIMARY KEY (project_id, user_id)
);

CREATE TABLE sprints (
  id SERIAL PRIMARY KEY,
  project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  name VARCHAR(80) NOT NULL,
  starts_on DATE,
  ends_on DATE
);

CREATE TABLE tasks (
  id SERIAL PRIMARY KEY,
  project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  sprint_id INTEGER REFERENCES sprints(id),
  assignee_id INTEGER REFERENCES users(id),
  title VARCHAR(200) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'todo',
  story_points INTEGER,
  due_date DATE
);

CREATE TABLE labels (
  id SERIAL PRIMARY KEY,
  project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  label VARCHAR(40) NOT NULL,
  color VARCHAR(9) DEFAULT '#10b981'
);

CREATE TABLE task_labels (
  task_id INTEGER NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
  label_id INTEGER NOT NULL REFERENCES labels(id) ON DELETE CASCADE,
  PRIMARY KEY (task_id, label_id)
);

CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  task_id INTEGER NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
  author_id INTEGER NOT NULL REFERENCES users(id),
  body TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT now()
);

Keep learning

• What Is an ER Diagram?
• How to Create an ER Diagram
• SQL to ER Diagram: The Complete Guide
• Primary Key vs Foreign Key
• All guides