Polymorphic Associations in SQL Design

1. What a Polymorphic Association is and where it comes from

A Polymorphic Association is a column pattern in which one table can reference rows in several different parent tables without a single dedicated foreign key existing for that purpose. Instead of a column post_id that points exclusively to posts, the table gets a combination of a type column such as commentable_type and a generic id column such as commentable_id. Depending on the content of the type column, the same id column refers sometimes to posts, sometimes to photos, sometimes to videos.

The typical example is a comment feature: posts, photos, and videos should all be commentable, but nobody wants to maintain three nearly identical tables post_comments, photo_comments, and video_comments. A Polymorphic Association seemingly solves this elegantly by making a single comments table responsible for all three cases. Tagging systems, generic attachments, or activity feeds also frequently rely on this pattern because at first glance it avoids redundancy.

Many ORMs such as Active Record or Eloquent generate this pattern automatically as soon as a developer declares a relationship as "polymorphic." This makes things convenient at the application layer: one model, one method, one query interface for all target types. The price for this convenience, however, is paid inside the database, precisely where relational systems are strongest, namely enforcing integrity rules, and that is exactly where a Polymorphic Association loses almost all of its effect.

2. The classic pattern: type column and generic id column

The basic shape of a Polymorphic Association is quickly explained: a text or enum column stores the name of the target table or model, an integer column stores the primary key of the referenced row. Application code decides at runtime, based on the value in the type column, against which table the id must actually be resolved. The database itself knows nothing of this logic, it only sees two independent columns with no enforced relationship.

-- Classic polymorphic association schema, without a real foreign key
CREATE TABLE comments (
  comment_id       SERIAL PRIMARY KEY,
  commentable_type VARCHAR(50)  NOT NULL,   -- 'post', 'photo', 'video'
  commentable_id   INTEGER      NOT NULL,   -- points to posts, photos, or videos depending on type
  author_id        INTEGER      NOT NULL REFERENCES users(user_id),
  body             TEXT         NOT NULL,
  created_at       TIMESTAMPTZ  NOT NULL DEFAULT now()
);

-- Application code must know on its own which table is meant
-- SELECT * FROM comments WHERE commentable_type = 'post' AND commentable_id = 42;
-- SELECT * FROM comments WHERE commentable_type = 'photo' AND commentable_id = 17;

-- The database CANNOT verify whether commentable_id = 42
-- actually exists in the table named by commentable_type

At first glance the pattern looks attractive: a fourth commentable type, say product, can be introduced without writing a single migration. It is enough to insert rows with the new type string. This apparent flexibility is deceptive, though, because the type column is plain text without any safeguard. A typo such as 'Post' instead of 'post', a renamed model in the application, or a forgotten migration during a refactor all go completely unnoticed by the database, even though they make data practically unfindable.

3. Why this pattern breaks referential integrity

The fundamental reason a Polymorphic Association cannot be cleanly enforced with classic means lies in SQL itself: a foreign key constraint always refers to exactly one target table. There is no SQL syntax that states "this column references either table A, B, or C, depending on the value of another column." The database engine can therefore never verify whether commentable_id actually exists in the table named by commentable_type.

A concrete failure pattern shows up on delete: if a post is deleted, for example through a manual cleanup script or a bug in the deletion routine, the associated comments remain unchanged in the comments table. Nothing in the database marks these rows as invalid, because no foreign key exists that could trigger under ON DELETE CASCADE. The application suddenly shows comments for a post that no longer exists in list views, or a batch job crashes because a join against the wrong table finds no row.

Such orphaned rows rarely surface immediately. They accumulate unnoticed, especially after data migrations, partial deletions, or import jobs that touch one table but not the other. Weeks or months later, the consequences show up as broken links, empty detail pages, or wrong counters in reports, while the original cause, an unchecked Polymorphic Association, is long since no longer obvious.

4. Alternative 1: Exclusive Arc with multiple nullable foreign keys

The first alternative replaces the generic id column with several specific, nullable foreign key columns, one per possible target type. A CHECK constraint ensures that in every row exactly one of these columns is set while all others remain NULL. This pattern is called Exclusive Arc, because from the perspective of the comment row exactly one of the possible relationships is allowed to be active.

-- Exclusive Arc: real foreign keys, but one column per target type
CREATE TABLE comments (
  comment_id  SERIAL PRIMARY KEY,
  post_id     INTEGER REFERENCES posts(post_id)   ON DELETE CASCADE,
  photo_id    INTEGER REFERENCES photos(photo_id) ON DELETE CASCADE,
  video_id    INTEGER REFERENCES videos(video_id) ON DELETE CASCADE,
  author_id   INTEGER NOT NULL REFERENCES users(user_id),
  body        TEXT    NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),

  -- Exactly one of the three columns must be set, the others NULL
  CONSTRAINT exactly_one_parent CHECK (
    (post_id IS NOT NULL)::int +
    (photo_id IS NOT NULL)::int +
    (video_id IS NOT NULL)::int = 1
  )
);

The advantage is obvious: every column has a real foreign key, the database refuses to insert an invalid reference, and dependent comments are automatically removed via CASCADE as soon as the parent row disappears. The downside is scalability: every new commentable type requires a real schema migration with an additional column and an adjustment of the CHECK constraint. With three or four types this stays manageable, with a dozen the table becomes unwieldy wide.

5. Alternative 2: separate junction tables per target type

The second alternative fully separates content and association: the comments table only holds generic comment information with no reference to any parent type at all. For every relationship a dedicated, lean junction table exists with two real foreign keys that establish the link between comment and concrete target type.

-- Base table with no reference to any parent type at all
CREATE TABLE comments (
  comment_id  SERIAL PRIMARY KEY,
  author_id   INTEGER NOT NULL REFERENCES users(user_id),
  body        TEXT    NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- One junction table per target type, each with real foreign keys
CREATE TABLE post_comments (
  comment_id  INTEGER PRIMARY KEY REFERENCES comments(comment_id) ON DELETE CASCADE,
  post_id     INTEGER NOT NULL REFERENCES posts(post_id) ON DELETE CASCADE
);

CREATE TABLE photo_comments (
  comment_id  INTEGER PRIMARY KEY REFERENCES comments(comment_id) ON DELETE CASCADE,
  photo_id    INTEGER NOT NULL REFERENCES photos(photo_id) ON DELETE CASCADE
);

-- Query: all comments belonging to a given post
SELECT c.* FROM comments c
JOIN post_comments pc ON pc.comment_id = c.comment_id
WHERE pc.post_id = 42;

This pattern enforces full referential integrity in both directions without bloating the base table with unused nullable columns. A new target type simply means a new, small junction table, without touching the existing comments table. The downside is the extra join effort on every query, plus the need to ensure, via application logic or an additional constraint, that a comment does not appear in several junction tables at the same time.

6. Alternative 3: a shared supertype table as dispatch layer

The third alternative solves the problem at its root by introducing a thin shared table that every commentable entity references through its own foreign key. This supertype table carries almost no business data itself, it serves purely as a dispatch layer: a single primary key that the comments table can now reference with a single, real foreign key, because there is only one target object left.

-- Thin supertype table: dispatch only, no business data
CREATE TABLE commentables (
  commentable_id   SERIAL PRIMARY KEY,
  entity_type      VARCHAR(20) NOT NULL CHECK (entity_type IN ('post','photo','video'))
);

-- Every subtype table references the supertype table 1:1
CREATE TABLE posts (
  post_id         INTEGER PRIMARY KEY REFERENCES commentables(commentable_id),
  title           VARCHAR(255) NOT NULL,
  body            TEXT
);

CREATE TABLE photos (
  photo_id        INTEGER PRIMARY KEY REFERENCES commentables(commentable_id),
  storage_path    VARCHAR(500) NOT NULL
);

-- comments now references only ONE table with a real foreign key
CREATE TABLE comments (
  comment_id      SERIAL PRIMARY KEY,
  commentable_id  INTEGER NOT NULL REFERENCES commentables(commentable_id) ON DELETE CASCADE,
  author_id       INTEGER NOT NULL REFERENCES users(user_id),
  body            TEXT    NOT NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

With this, the Polymorphic Association is, from the database's point of view, no longer a polymorphic relationship at all, but a perfectly ordinary, fully verifiable foreign key relationship to exactly one table. When a post is deleted, ON DELETE CASCADE automatically removes the entry in commentables and thereby every comment attached to it. The price is an extra join whenever application code needs to know, starting from a comment, whether the target is a post or a photo, plus a certain discipline when creating new subtype tables that must consistently be linked through the supertype table.

7. Performance and indexing for polymorphic foreign keys

Regardless of which pattern is chosen, indexing has a direct impact on query speed. In the classic type-plus-id pattern, a single index on commentable_id alone is useless, because the same id value can occur multiple times across different types. What is needed is a composite index across both columns in the order (commentable_type, commentable_id), so that the query planner can use both filter conditions together.

-- Composite index is mandatory for the classic Polymorphic Association
CREATE INDEX idx_comments_commentable
  ON comments (commentable_type, commentable_id);

-- Without this index the database may scan the entire table,
-- because commentable_id alone does not allow unique filtering
EXPLAIN ANALYZE
SELECT * FROM comments
WHERE commentable_type = 'post' AND commentable_id = 42;

-- With heavily skewed types (e.g. 95% 'post', 5% 'video')
-- the planner may switch to a bitmap scan for rare types
-- but prefer a full table scan for frequent types

Another performance aspect concerns queries that need to aggregate across all target types, for example "show the ten most recently commented objects, regardless of whether they are posts, photos, or videos." With the classic Polymorphic Association, this often requires several separate queries followed by UNION ALL, since a single join against several possible target tables does not exist in standard SQL. The supertype solution from section six simplifies exactly this case, because a single join against the dispatch table is already enough to load metadata for all types together.

8. Application level consistency checks as a compromise

In some projects the classic Polymorphic Association remains the pragmatic choice despite its weaknesses, for example in early prototyping phases, with very frequently changing target types, or when an existing ORM framework prescribes the pattern outright. In these cases at least a minimal compromise pays off: a CHECK constraint on the type column that only allows known values, at least preventing typos and silent drift caused by renamed models.

In addition, a regularly running consistency check, usually as a nightly batch job, helps by checking per type whether all referenced ids actually still exist, and reporting deviations to a monitoring system. This does not replace real referential integrity, but it prevents orphaned rows from going unnoticed for months. Anyone planning a new project should still prefer one of the three alternatives from sections four through six from the start, because cleaning up a historically grown Polymorphic Association later is considerably more effort than choosing correctly at the initial design stage.

9. Polymorphic Associations compared

The following table places the four approaches to a Polymorphic Association side by side, evaluated by referential integrity, effort for a new target type, and the complexity of typical queries.

10. Summary

A Polymorphic Association solves a real modeling problem, namely connecting a table to several possible parent types, but the classic type-plus-id pattern pays for this flexibility by losing referential integrity. No SQL foreign key can validate against multiple target tables at once, which can create orphaned rows, broken links, and silent data inconsistencies that often surface only much later.

Exclusive Arc, separate junction tables, and a shared supertype table solve the same business problem with real, database-verified foreign keys. Which of the three alternatives fits depends on the number of target types, the change frequency, and typical query patterns. Anyone who still deliberately uses a Polymorphic Association should at least plan for CHECK constraints on the type column and regular consistency checks to mitigate the biggest risks.

11. FAQ: Polymorphic Associations in SQL Design