PostgreSQL example

PostgreSQL Table Design Example

A PostgreSQL table design example using uuid keys, timestamptz, jsonb, partial indexes, and foreign keys.

Create an ERD in ErdForge View all examples

Scenario

PostgreSQL Table Design Example

A SaaS project model for accounts, workspaces, memberships, and audit logs using PostgreSQL-friendly choices.

Key types

uuid, timestamptz, jsonb

Key relation

accounts 1:N workspaces

Main caution

Soft deletes and partial indexes

Requirements

An account owns many workspaces.
A user can have different roles per workspace.
Audit logs need structured metadata that varies by event type.

Table design

accountsCustomer organizations

id uuid PK
name text
plan text
created_at timestamptz

usersUsers

id uuid PK
email citext UNIQUE
display_name text
created_at timestamptz

workspacesWorkspaces

id uuid PK
account_id uuid FK
name text
deleted_at timestamptz

workspace_membersMemberships

workspace_id uuid PK/FK
user_id uuid PK/FK
role text
added_at timestamptz

audit_eventsAudit events

id uuid PK
workspace_id uuid FK
actor_user_id uuid FK
event_type text
metadata jsonb

Relationships

accounts 1:N workspacesusers N:M workspaces through workspace_membersworkspaces 1:N audit_eventsusers 1:N audit_events

Design notes

Use citext or lower indexes for email

To prevent case-sensitive email duplicates, use the citext extension or a unique index on lower(email).

Use partial indexes for soft delete

A unique index with WHERE deleted_at IS NULL can enforce active workspace names without blocking historical rows.

Use jsonb for variable metadata

jsonb works well for event metadata, but fields used for joins or frequent filters should be real columns.

Implementation checks

Use gen_random_uuid() as the uuid default.
Store timestamps as timestamptz.
Use a composite primary key for workspace_members.

SQL example

CREATE TABLE workspace_members (
  workspace_id uuid REFERENCES workspaces(id),
  user_id uuid REFERENCES users(id),
  role text NOT NULL CHECK (role IN ('owner', 'editor', 'viewer')),
  added_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (workspace_id, user_id)
);

More ERD examples

Use these structures as a starting point, or open a demo canvas before creating an account.