Project tracker – ER diagram

PostgreSQL · 4 tables · 4 declared foreign keys · no application data

project_members

project_idbigintPKFK

user_idbigintPKFK

rolevarchar(20)

Composite primary key (project_id, user_id): only the pair is unique. Either column on its own may repeat.

projects

idbigintPK

namevarchar(120)

users

idbigintPK

emailvarchar(255)UQ

tasks

idbigintPK

project_idbigintFK

assignee_idbigintFKNULL

titlevarchar(200)

external_ticket_idvarchar(80)NULL

external_ticket_id is an identifier from an external issue tracker, not a local foreign key (column comment). No connector is drawn for it.

Relationships

project_members.project_id → projects.id — 1 : 0..N. Each membership belongs to exactly one project (NOT NULL). A project has zero or many memberships; project_id is not unique on its own (it is only half of the composite key).

project_members.user_id → users.id — 1 : 0..N. Each membership belongs to exactly one user (NOT NULL). A user has zero or many memberships; user_id is not unique on its own.

tasks.project_id → projects.id — 1 : 0..N. Each task belongs to exactly one project (NOT NULL). A project has zero or many tasks.

tasks.assignee_id → users.id — 0..1 : 0..N. A task has zero or one assignee (the column is nullable). A user is assigned zero or many tasks.

Legend

PK primary key (two PK rows in one card = composite key; see the card note)

FK foreign key

UQ unique constraint

NULL column allows NULL; all other columns are NOT NULL

connector runs from the foreign-key column; the arrow points at the referenced primary key

1 exactly one · 0..1 zero or one · 0..N zero or many — derived from the foreign key, its NOT NULL constraint and whether the column is unique