Project tracker

PostgreSQL ER diagram · 4 tables · 4 declared foreign keys

users Table

Column	SQL type	Keys
id	bigintNOT NULL · PK	PK
email	varchar(255)NOT NULL	UQ

Primary key: id

project_members Table

Column	SQL type	Keys
project_id	bigintNOT NULL	PK¹FK
user_id	bigintNOT NULL	PK¹FK
role	varchar(20)NOT NULL

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

tasks Table

Column	SQL type	Keys
id	bigintNOT NULL · PK	PK
project_id	bigintNOT NULL	FK
assignee_id	bigintNULL allowed	FK
title	varchar(200)NOT NULL
external_ticket_id	varchar(80)NULL allowed

external_ticket_id identifies an external issue; it is not a local FK.

projects Table

Column	SQL type	Keys
id	bigintNOT NULL · PK	PK
name	varchar(120)NOT NULL

Primary key: id

Declared relationships

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

Reading the diagram

PK Primary key, unique and non-null. FK Declared foreign key. UQ Declared single-column UNIQUE. PK¹ identifies members of one composite primary key.

1 exactly one · 0..1 optional one · 0..* zero or many. A number beside a card counts rows in that table for one row at the other end. Line labels name the referencing FK column.

Required FKs reference exactly one parent; nullable assignee_id permits none. No FK column is individually unique, so each parent can have many referencing rows. No constraint requires a parent to have any children.

Only declared FKs are drawn. The schema does not require an assignee to belong to the task’s project.