Project tracker — ER diagram

PostgreSQL. Four tables, four declared foreign keys. Cardinality is derived from the foreign keys, column nullability and uniqueness.

project_id user_id project_id assignee_id 1 0..N 1 0..N 1 0..N 0..1 0..N
projects
idPKbigint
namevarchar(120)
users
idPKbigint
emailUvarchar(255)
project_members
project_idPK 1/2FKbigint
user_idPK 2/2FKbigint
rolevarchar(20)
Composite primary key (project_id, user_id). Only the pair is unique. Either column on its own may repeat across rows.
tasks
idPKbigint
project_idFKbigint
assignee_idFKNULLbigint
titlevarchar(200)
external_ticket_idNULLvarchar(80)
external_ticket_id is an identifier from an external issue tracker, not a local foreign key, so it draws no connector.

Relationships

  1. project_members.project_id → projects.id
    Each project_members row belongs to exactly one project (the column is NOT NULL). Each projects row has zero or many membership rows.
  2. project_members.user_id → users.id
    Each project_members row belongs to exactly one user (the column is NOT NULL). Each users row has zero or many membership rows.
  3. tasks.project_id → projects.id
    Each tasks row belongs to exactly one project (the column is NOT NULL). Each projects row has zero or many tasks.
  4. tasks.assignee_id → users.id
    Each tasks row has zero or one assignee (the column is nullable). Each users row is the assignee of zero or many tasks.

No foreign key is unique or uniquely indexed, so no relationship here is one-to-one. The two foreign keys in project_members together form its primary key, which makes that table a many-to-many join between projects and users, carrying a role of its own.

Legend

PK primary key
PK 1/2 one part of a composite primary key
FK foreign key
U unique constraint
NULL nullable column
exactly one
zero or one
zero or many