Entity-Relationship Modeling

Q: How do I model a optional one-to-one relationship?

Put a nullable UNIQUE FK on one side: The UNIQUE on person_id enforces at most one passport per person.

Q: How do I document cardinality for non-DB readers?

Use a simple ERD legend in your repo (1 ----< N) and mirror it in ADRs. Keep the executable truth in DDL, not only in diagrams.

Q: Can two tables share the same primary key for 1:1?

Yes - common for extension tables:

Q: What is the difference between weak and strong entities in practice?

A "weak" entity (order line items) cannot exist without its parent (order). Enforce with NOT NULL FK plus ON DELETE CASCADE or RESTRICT depending on business rules.

Q: How many foreign keys are too many on one table?

There is no hard limit, but wide tables with many nullable FKs often signal a missing junction or state machine table. Split when more than a handful of optional relationships accumulate.

Q: Should junction tables have their own surrogate PK?

A composite PK on the two FKs is enough when the pair is unique. Add a surrogate id only if ORMs or APIs require a single-column identifier for the link row itself.

Q: How do optionalities affect DELETE behavior?

ON DELETE SET NULL pairs with optional FKs. Required FKs typically use RESTRICT or CASCADE - pick based on whether child rows should survive parent deletion.

Cardinality, optionalities, and naming conventions turn domain nouns into PostgreSQL tables that stay correct under load.

Recipe

Quick-reference recipe card - copy-paste ready.

-- One-to-many: FK on the "many" table
CREATE TABLE posts (
  post_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  author_id  bigint NOT NULL REFERENCES authors (author_id)
);
 
-- Optional relationship: nullable FK
CREATE TABLE posts (
  post_id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  editor_id     bigint REFERENCES editors (editor_id)  -- NULL = unassigned
);
 
-- Many-to-many: junction table with two FKs
CREATE TABLE tag_assignments (
  post_id  bigint NOT NULL REFERENCES posts (post_id) ON DELETE CASCADE,
  tag_id   bigint NOT NULL REFERENCES tags (tag_id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

When to reach for this:

Greenfield schema design before the first migration ships.
Refactoring a fan-out join caused by a missing junction table.
Reviewing whether a nullable FK or a separate link table is the right shape.
Naming tables and columns so JOIN paths read like the domain.

Working Example

BEGIN;
 
CREATE TABLE authors (
  author_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       text NOT NULL
);
 
CREATE TABLE editors (
  editor_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       text NOT NULL
);
 
CREATE TABLE posts (
  post_id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  author_id   bigint NOT NULL REFERENCES authors (author_id),
  editor_id   bigint REFERENCES editors (editor_id),
  title       text NOT NULL,
  published   boolean NOT NULL DEFAULT false
);
 
CREATE TABLE tags (
  tag_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  label   text NOT NULL UNIQUE
);
 
CREATE TABLE post_tags (
  post_id  bigint NOT NULL REFERENCES posts (post_id) ON DELETE CASCADE,
  tag_id   bigint NOT NULL REFERENCES tags (tag_id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);
 
CREATE INDEX posts_author_id_idx ON posts (author_id);
CREATE INDEX posts_editor_id_idx ON posts (editor_id) WHERE editor_id IS NOT NULL;
CREATE INDEX post_tags_tag_id_idx ON post_tags (tag_id);
 
COMMIT;

What this demonstrates:

Required vs optional relationships via NOT NULL vs nullable FK columns.
M:N resolved with post_tags instead of duplicating tag rows on posts.
Partial index on optional editor_id keeps index size small when most posts lack an editor.
ON DELETE CASCADE on junction rows when parent posts or tags are removed.

Deep Dive

Cardinality at a Glance

Relationship	PostgreSQL shape	FK location
1:1	Unique FK or shared PK	Either table; unique constraint required
1:N	FK on child	"Many" side
M:N	Junction table	Both FKs on link table
Optional	Nullable FK	Child allows NULL
Required	`NOT NULL` FK	Child always has parent

Naming Conventions

Tables: plural nouns (orders, line_items) or singular if your org standardizes on singular - pick one and lint it.
PK columns: <entity>_id (order_id) with bigint GENERATED ALWAYS AS IDENTITY.
FK columns: match referenced PK name (customer_id references customers.customer_id).
Junction tables: <left>_<right> or <left>_<right>_map (order_items, user_roles).

Optionality Rules

-- Every order must have a customer (required)
customer_id bigint NOT NULL REFERENCES customers (customer_id)
 
-- Shipment may not exist yet (optional)
shipment_id bigint REFERENCES shipments (shipment_id)

Required relationships: NOT NULL + FK.
Optional relationships: nullable FK; use partial indexes for selective lookups.
Do not model optionality only in application code - the database should reject impossible states.

SQL Notes

-- Detect orphan rows if FKs were added late (should return 0)
SELECT o.order_id
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;
 
-- 1:1 enforced with UNIQUE on the FK column
CREATE TABLE user_profiles (
  user_id    bigint PRIMARY KEY REFERENCES users (user_id),
  bio        text
);

Gotchas

M:N modeled as duplicate columns - storing tag1, tag2, tag3 on posts breaks normalization and blocks indexing. Fix: junction table with (post_id, tag_id).
FK on the wrong side - putting order_id on customers for a 1:N order list. Fix: FK on orders.customer_id.
Nullable FK treated as required in queries - inner joins drop rows with unassigned editors. Fix: LEFT JOIN when the relationship is optional.
Missing index on FK columns - nested loop joins degrade on large tables. Fix: CREATE INDEX ON child (parent_id).
Symmetric junction without uniqueness - duplicate (post_id, tag_id) pairs. Fix: composite PRIMARY KEY or UNIQUE constraint.

Alternatives

Alternative	Use When	Don't Use When
PostgreSQL `ENUM` for status	Small, stable value sets	Values change weekly (migration pain)
`jsonb` for nested attributes	Schema varies per row, read-heavy	You need FK integrity on nested IDs
Composite natural keys	Strong business identifiers exist globally	IDs are composite across tenants or regions
Single-table inheritance	Few subtypes, shared columns	Subtypes diverge with many nullable columns

FAQs

Should primary keys always be surrogate bigint identity columns?

Surrogate keys are the default for OLTP because they are narrow, stable, and index-friendly. Use natural keys (email, SKU) as UNIQUE constraints when the business guarantees global uniqueness.

How do I model a optional one-to-one relationship?

Put a nullable UNIQUE FK on one side:

CREATE TABLE passports (
  passport_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  person_id    bigint NOT NULL UNIQUE REFERENCES persons (person_id),
  number       text NOT NULL
);

The UNIQUE on person_id enforces at most one passport per person.

When is a junction table overkill?

When the association has no attributes and each side appears at most once - a 1:1 can use a single FK. If the link carries metadata (assigned_at, role), use a junction or link table even for low cardinality.

Should I name FK constraints explicitly?

Yes for operability:

CONSTRAINT orders_customer_id_fkey
  FOREIGN KEY (customer_id) REFERENCES customers (customer_id)

Explicit names make migration diffs and error messages readable in production logs.

How do I document cardinality for non-DB readers?

Use a simple ERD legend in your repo (1 --< N, N >--< N) and mirror it in ADRs. Keep the executable truth in DDL, not only in diagrams.

Can two tables share the same primary key for 1:1?

Yes - common for extension tables:

CREATE TABLE users (user_id bigint PRIMARY KEY, email text NOT NULL);
CREATE TABLE user_settings (
  user_id bigint PRIMARY KEY REFERENCES users (user_id),
  theme   text NOT NULL DEFAULT 'light'
);

What is the difference between weak and strong entities in practice?

A "weak" entity (order line items) cannot exist without its parent (order). Enforce with NOT NULL FK plus ON DELETE CASCADE or RESTRICT depending on business rules.

How many foreign keys are too many on one table?

There is no hard limit, but wide tables with many nullable FKs often signal a missing junction or state machine table. Split when more than a handful of optional relationships accumulate.

Should junction tables have their own surrogate PK?

A composite PK on the two FKs is enough when the pair is unique. Add a surrogate id only if ORMs or APIs require a single-column identifier for the link row itself.

How do optionalities affect DELETE behavior?

ON DELETE SET NULL pairs with optional FKs. Required FKs typically use RESTRICT or CASCADE - pick based on whether child rows should survive parent deletion.

Data Modeling Basics - conceptual to physical flow
Domain-Driven Schema Boundaries - grouping entities by context
Normalization Basics - eliminating update anomalies
Missing Foreign Keys - integrity failures without FKs
Fan-Out Join Explosion - cardinality mistakes in queries

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Recipe

Quick-reference recipe card - copy-paste ready.

-- One-to-many: FK on the "many" table
CREATE TABLE posts (
  post_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  author_id  bigint NOT NULL REFERENCES authors (author_id)
);
 
-- Optional relationship: nullable FK
CREATE TABLE posts (
  post_id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  editor_id     bigint REFERENCES editors (editor_id)  -- NULL = unassigned
);
 
-- Many-to-many: junction table with two FKs
CREATE TABLE tag_assignments (
  post_id  bigint NOT NULL REFERENCES posts (post_id) ON DELETE CASCADE,
  tag_id   bigint NOT NULL REFERENCES tags (tag_id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

When to reach for this:

Greenfield schema design before the first migration ships.
Refactoring a fan-out join caused by a missing junction table.
Reviewing whether a nullable FK or a separate link table is the right shape.
Naming tables and columns so JOIN paths read like the domain.

Working Example

BEGIN;
 
CREATE TABLE authors (
  author_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       text NOT NULL
);
 
CREATE TABLE editors (
  editor_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       text NOT NULL
);
 
CREATE TABLE posts (
  post_id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  author_id   bigint NOT NULL REFERENCES authors (author_id),
  editor_id   bigint REFERENCES editors (editor_id),
  title       text NOT NULL,
  published   boolean NOT NULL DEFAULT false
);
 
CREATE TABLE tags (
  tag_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  label   text NOT NULL UNIQUE
);
 
CREATE TABLE post_tags (
  post_id  bigint NOT NULL REFERENCES posts (post_id) ON DELETE CASCADE,
  tag_id   bigint NOT NULL REFERENCES tags (tag_id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);
 
CREATE INDEX posts_author_id_idx ON posts (author_id);
CREATE INDEX posts_editor_id_idx ON posts (editor_id) WHERE editor_id IS NOT NULL;
CREATE INDEX post_tags_tag_id_idx ON post_tags (tag_id);
 
COMMIT;

What this demonstrates:

Required vs optional relationships via NOT NULL vs nullable FK columns.
M:N resolved with post_tags instead of duplicating tag rows on posts.
Partial index on optional editor_id keeps index size small when most posts lack an editor.
ON DELETE CASCADE on junction rows when parent posts or tags are removed.

Deep Dive

Cardinality at a Glance

Relationship	PostgreSQL shape	FK location
1:1	Unique FK or shared PK	Either table; unique constraint required
1:N	FK on child	"Many" side
M:N	Junction table	Both FKs on link table
Optional	Nullable FK	Child allows NULL
Required	`NOT NULL` FK	Child always has parent

Naming Conventions

Tables: plural nouns (orders, line_items) or singular if your org standardizes on singular - pick one and lint it.
PK columns: <entity>_id (order_id) with bigint GENERATED ALWAYS AS IDENTITY.
FK columns: match referenced PK name (customer_id references customers.customer_id).
Junction tables: <left>_<right> or <left>_<right>_map (order_items, user_roles).

Optionality Rules

-- Every order must have a customer (required)
customer_id bigint NOT NULL REFERENCES customers (customer_id)
 
-- Shipment may not exist yet (optional)
shipment_id bigint REFERENCES shipments (shipment_id)

Required relationships: NOT NULL + FK.
Optional relationships: nullable FK; use partial indexes for selective lookups.
Do not model optionality only in application code - the database should reject impossible states.

SQL Notes

-- Detect orphan rows if FKs were added late (should return 0)
SELECT o.order_id
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;
 
-- 1:1 enforced with UNIQUE on the FK column
CREATE TABLE user_profiles (
  user_id    bigint PRIMARY KEY REFERENCES users (user_id),
  bio        text
);

Gotchas

M:N modeled as duplicate columns - storing tag1, tag2, tag3 on posts breaks normalization and blocks indexing. Fix: junction table with (post_id, tag_id).
FK on the wrong side - putting order_id on customers for a 1:N order list. Fix: FK on orders.customer_id.
Nullable FK treated as required in queries - inner joins drop rows with unassigned editors. Fix: LEFT JOIN when the relationship is optional.
Missing index on FK columns - nested loop joins degrade on large tables. Fix: CREATE INDEX ON child (parent_id).
Symmetric junction without uniqueness - duplicate (post_id, tag_id) pairs. Fix: composite PRIMARY KEY or UNIQUE constraint.

Alternatives

Alternative	Use When	Don't Use When
PostgreSQL `ENUM` for status	Small, stable value sets	Values change weekly (migration pain)
`jsonb` for nested attributes	Schema varies per row, read-heavy	You need FK integrity on nested IDs
Composite natural keys	Strong business identifiers exist globally	IDs are composite across tenants or regions
Single-table inheritance	Few subtypes, shared columns	Subtypes diverge with many nullable columns

FAQs

Should primary keys always be surrogate bigint identity columns?

Surrogate keys are the default for OLTP because they are narrow, stable, and index-friendly. Use natural keys (email, SKU) as UNIQUE constraints when the business guarantees global uniqueness.

How do I model a optional one-to-one relationship?

Put a nullable UNIQUE FK on one side:

CREATE TABLE passports (
  passport_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  person_id    bigint NOT NULL UNIQUE REFERENCES persons (person_id),
  number       text NOT NULL
);

The UNIQUE on person_id enforces at most one passport per person.

When is a junction table overkill?

Should I name FK constraints explicitly?

Yes for operability:

CONSTRAINT orders_customer_id_fkey
  FOREIGN KEY (customer_id) REFERENCES customers (customer_id)

Explicit names make migration diffs and error messages readable in production logs.

How do I document cardinality for non-DB readers?

Use a simple ERD legend in your repo (1 --< N, N >--< N) and mirror it in ADRs. Keep the executable truth in DDL, not only in diagrams.

Can two tables share the same primary key for 1:1?

Yes - common for extension tables:

CREATE TABLE users (user_id bigint PRIMARY KEY, email text NOT NULL);
CREATE TABLE user_settings (
  user_id bigint PRIMARY KEY REFERENCES users (user_id),
  theme   text NOT NULL DEFAULT 'light'
);

What is the difference between weak and strong entities in practice?

A "weak" entity (order line items) cannot exist without its parent (order). Enforce with NOT NULL FK plus ON DELETE CASCADE or RESTRICT depending on business rules.

How many foreign keys are too many on one table?

There is no hard limit, but wide tables with many nullable FKs often signal a missing junction or state machine table. Split when more than a handful of optional relationships accumulate.

Should junction tables have their own surrogate PK?

A composite PK on the two FKs is enough when the pair is unique. Add a surrogate id only if ORMs or APIs require a single-column identifier for the link row itself.

How do optionalities affect DELETE behavior?

ON DELETE SET NULL pairs with optional FKs. Required FKs typically use RESTRICT or CASCADE - pick based on whether child rows should survive parent deletion.

Data Modeling Basics - conceptual to physical flow
Domain-Driven Schema Boundaries - grouping entities by context
Normalization Basics - eliminating update anomalies
Missing Foreign Keys - integrity failures without FKs
Fan-Out Join Explosion - cardinality mistakes in queries

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Entity-Relationship Modeling

Search Documentation

Entity-Relationship Modeling