Crowsfoot

Project management (SaaS) database schema: ER diagram and SQL

Most SaaS products follow this shape: a customer account (the organization), the people who belong to it, and the work they do. This is a common starting point for task trackers, CRMs, and team tools.

Open this schema in the editor Jump to the SQL
organizations PK id bigint name varchar plan varchar users PK id bigint email varchar name varchar memberships PK organization_id bigint PK user_id bigint role varchar projects PK id bigint FK organization_id bigint name varchar archived boolean tasks PK id bigint FK project_id bigint FK assignee_id bigint title varchar status varchar due_on date

Tables in this schema

organizations

The customer account (tenant) that owns projects.

users

People with a login.

memberships

Which users belong to which organizations, and in what role. A junction table.

projects

Containers of work, owned by an organization.

tasks

Units of work in a project, optionally assigned to a user.

How the tables relate

  • memberships is a junction table between organizations and users: many-to-many.
  • projects.organization_id references organizations.id: one row in organizations can have many rows in projects.
  • tasks.project_id references projects.id: one row in projects can have many rows in tasks.
  • tasks.assignee_id references users.id: one row in users can have many rows in tasks.

SQL (PostgreSQL)

Copy it into your database, or open it in Crowsfoot to edit the diagram, add notes, and export again.

CREATE TABLE organizations (
  id bigint PRIMARY KEY,
  name varchar(120) NOT NULL,
  plan varchar(20) NOT NULL DEFAULT 'free'
);

CREATE TABLE users (
  id bigint PRIMARY KEY,
  email varchar(255) NOT NULL,
  name varchar(120) NOT NULL
);

CREATE TABLE memberships (
  organization_id bigint NOT NULL REFERENCES organizations (id),
  user_id bigint NOT NULL REFERENCES users (id),
  role varchar(20) NOT NULL DEFAULT 'member',
  PRIMARY KEY (organization_id, user_id)
);

CREATE TABLE projects (
  id bigint PRIMARY KEY,
  organization_id bigint NOT NULL REFERENCES organizations (id),
  name varchar(160) NOT NULL,
  archived boolean NOT NULL DEFAULT false
);

CREATE TABLE tasks (
  id bigint PRIMARY KEY,
  project_id bigint NOT NULL REFERENCES projects (id),
  assignee_id bigint REFERENCES users (id),
  title varchar(200) NOT NULL,
  status varchar(20) NOT NULL DEFAULT 'todo',
  due_on date
);

Design notes

  • Every row ultimately belongs to an organization, which makes tenant isolation (for example with row-level security) straightforward.
  • Roles live on memberships rather than users, because one person can be an admin in one organization and a guest in another.
  • assignee_id is optional so tasks can start out unassigned.

More example schemas

New to ER diagrams? Read the crow’s foot notation guide.