Entity-Relationship Modeling

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

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

FAQs

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
);

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

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'
);

Related

• 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+.