Postgres: Best design pattern for “Exclusive Arc” (Polymorphic) relationships? (Nullable FKs vs. alternatives)

by

Summary

This postmortem examines a common relational‑design failure: implementing Exclusive Arc (a.k.a. Polymorphic) relationships in PostgreSQL using multiple nullable foreign keys. The pattern works at first, but it becomes brittle, hard to extend, and difficult to enforce correctly. Senior engineers eventually replace it with cleaner, constraint‑friendly designs.

Root Cause

The root cause is the assumption that “nullable FKs + a CHECK constraint” is a scalable way to model polymorphic relationships. It is not. The design breaks down because:

  • Each new parent entity requires a schema migration.
  • Referential integrity becomes scattered across many columns.
  • Enforcing “exactly one parent” requires complex constraints.
  • Query logic becomes increasingly conditional and error‑prone.

Why This Happens in Real Systems

Real systems drift into this pattern because:

  • ORMs encourage table-per-entity thinking, leading to many FK columns.
  • Developers want strict FK enforcement, which generic polymorphic patterns don’t provide.
  • Initial requirements seem small, so the design feels “good enough.”
  • Exclusive Arc modeling is rarely taught, so teams reinvent it poorly.

Real-World Impact

Teams eventually feel the pain:

  • Schema bloat as more nullable columns accumulate.
  • Hard-to-maintain constraints that break during migrations.
  • Performance degradation from wide tables and sparse indexes.
  • Application bugs caused by rows with multiple or zero parents.
  • Operational friction when adding new entity types.

Example or Code (if necessary and relevant)

Below is the canonical senior‑engineer solution: a single parent table representing all possible entities, with each real entity referencing it.

CREATE TABLE notification_parent (
    id BIGSERIAL PRIMARY KEY,
    entity_type TEXT NOT NULL,
    entity_id BIGINT NOT NULL,
    UNIQUE (entity_type, entity_id)
);

CREATE TABLE notification_log (
    id BIGSERIAL PRIMARY KEY,
    parent_id BIGINT NOT NULL REFERENCES notification_parent(id) ON DELETE CASCADE,
    message TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Each concrete entity then inserts a row into notification_parent and stores its own ID and type. This preserves referential integrity, avoids nullable FKs, and requires zero schema changes when adding new entity types.

How Senior Engineers Fix It

Senior engineers typically choose one of these patterns:

  • The “Parent Table” pattern (shown above): scalable, clean, and preserves integrity.
  • Partitioned notification_log tables: one child table per entity type, sharing a common parent.
  • A join table per entity type: small, clean, and avoids nullable columns.
  • A domain-specific event system: decouples notifications from entity tables entirely.

Key principles they follow:

  • Avoid schema changes for new entity types.
  • Centralize referential integrity.
  • Keep constraints simple and enforceable.
  • Design for future extensibility, not current minimalism.

Why Juniors Miss It

Juniors often miss the deeper implications because:

  • Nullable FKs feel “simple” and appear to work early on.
  • They underestimate schema evolution, assuming few entity types.
  • They focus on ORM convenience, not relational modeling.
  • They haven’t seen Exclusive Arc failures at scale, so the risks seem theoretical.
  • They don’t yet think in terms of long-term maintainability, only immediate correctness.

The result is a design that works for months but becomes a liability for years.

Leave a Comment