Project management Schema

Interactive diagram

Drag to pan, scroll to zoom. Open it in the full editor to edit, rearrange and export.

About this schema

Projects contain tasks; tasks are assigned to users through a join table, carry many-to-many labels, and accumulate comments.

Schema (SQL)

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(120)
);
CREATE TABLE projects (
  id SERIAL PRIMARY KEY,
  key VARCHAR(10) UNIQUE, name VARCHAR(160),
  lead_id INT REFERENCES users(id)
);
CREATE TABLE tasks (
  id SERIAL PRIMARY KEY,
  project_id INT NOT NULL REFERENCES projects(id),
  title VARCHAR(200), status VARCHAR(20),
  priority SMALLINT, due_date DATE,
  reporter_id INT REFERENCES users(id)
);
CREATE TABLE task_assignees (
  task_id INT NOT NULL REFERENCES tasks(id),
  user_id INT NOT NULL REFERENCES users(id),
  PRIMARY KEY (task_id, user_id)
);
CREATE TABLE labels (
  id SERIAL PRIMARY KEY,
  name VARCHAR(40) UNIQUE, color VARCHAR(7)
);
CREATE TABLE task_labels (
  task_id INT NOT NULL REFERENCES tasks(id),
  label_id INT NOT NULL REFERENCES labels(id),
  PRIMARY KEY (task_id, label_id)
);
CREATE TABLE task_comments (
  id SERIAL PRIMARY KEY,
  task_id INT NOT NULL REFERENCES tasks(id),
  author_id INT NOT NULL REFERENCES users(id),
  body TEXT, created_at TIMESTAMP
);

Open in the editor → to export it as PNG, SVG, Mermaid or DBML.

More examples