Project tracker

PostgreSQL schema · 4 tables · 4 declared foreign keys. Endpoint labels show how many records may relate to one record at the opposite end.

Project tracker foreign-key relationships Users and projects each have zero or more project memberships. Each membership requires one user and one project. Each task requires one project and optionally references one assignee. Users and projects may each be referenced by zero or more tasks. Connectors occupy the spaces between table cards. 1 0..* user_id 0..1 0..* assignee_id 0..* 1 project_id 0..* 1 project_id

users

ColumnSQL typeRules
idbigintPK · NN
emailvarchar(255)UQ · NN

project_members

ColumnSQL typeRules
project_idbigintPK¹ · FK · NN
user_idbigintPK¹ · FK · NN
rolevarchar(20)NN

¹ Composite PK: (project_id, user_id).
The pair is unique; neither column is individually unique.

projects

ColumnSQL typeRules
idbigintPK · NN
namevarchar(120)NN

tasks

ColumnSQL typeRules
idbigintPK · NN
project_idbigintFK · NN
assignee_idbigintFK · NULL
titlevarchar(200)NN
external_ticket_idvarchar(80)NULL

external_ticket_id: An identifier from an external issue tracker, not a local foreign key.

Relationships

  • project_members.user_id → users.id
    Each membership references exactly 1 user; each user has 0..* memberships.
  • project_members.project_id → projects.id
    Each membership references exactly 1 project; each project has 0..* memberships.
  • tasks.project_id → projects.id
    Each task references exactly 1 project; each project has 0..* tasks.
  • tasks.assignee_id → users.id
    Each task references 0..1 user; each user may be assigned 0..* tasks.

Legend & constraints

PK primary key · FK foreign key · UQ unique · NN not null · NULL nullable. Primary keys imply uniqueness and not-null constraints.

1 exactly one · 0..1 zero or one · 0..* zero or many. Counts beside a table describe participation at that end.

PK¹ marks parts of a single composite key. None of the foreign-key columns is individually unique, so multiple child rows may reference the same parent. No constraint requires a parent to have any child rows.

Only declared foreign keys are connected. The schema does not require a task’s assignee to be a member of its project.