Polymorphic Associations

Introduction

Polymorphic associations store one identifier alongside a type discriminator so a row can point to one of several parent tables. The pattern gives application code a uniform API, but a standard foreign key cannot follow a value that may refer to different tables. Referential integrity, delete behavior, and some query planning work therefore move into application code and operational discipline.

Mental model: parent_id says "which ID?" and parent_type says "in which table?" The database can index that pair, but it cannot treat the pair as one ordinary foreign key without additional constraints or triggers. If the association carries important business data, prefer a schema where the database can validate the target directly.

TL;DR

• Polymorphic associations store a foreign key plus a type discriminator column to point to one of several tables: parent_id = 42, parent_type = 'Post'.
• A standard foreign key cannot enforce referential integrity for a value that may point to any of N tables. Without additional triggers or application logic, orphaned rows and inconsistent deletes can accumulate.
• You also can't create a standard foreign key constraint on parent_id. Queries normally need both (parent_id, parent_type); a composite index can help, but it is a less direct access path than a typed foreign key for that parent.
• Fix: Use separate join tables per association type (normalised) or nullable FK columns per parent type with a check constraint.
• When it's acceptable: ORM-managed polymorphism (Rails, Django) where application-layer code handles cascades and you have integration tests covering delete paths.

What It Is

You're building a comments system. Comments can be left on Posts, Videos, or Products. A developer introduces a "flexible" schema:

CREATE TABLE comments (
    id          BIGINT PRIMARY KEY,
    body        TEXT,
    user_id     BIGINT,
    parent_id   BIGINT,           -- points to a post, video, or product
    parent_type VARCHAR(20)       -- 'Post', 'Video', 'Product'
);

Concrete before/after: comment ownership

Before: a comment stores parent_id and parent_type, so the application must interpret the target and implement delete behavior.

After: a comment stores one typed foreign key such as post_id, or is connected through a post_comments, video_comments, or product_comments table. The database can then validate the target and apply the intended cascade. The concrete schemas and queries appear in the remediation section below.

This looks clean. One table, one query pattern, works for all three parent types. It often feels elegant at design time.

Then someone deletes a Video. Does the database automatically delete its comments? No. There's no FOREIGN KEY (parent_id) REFERENCES videos(id). The database has no idea that parent_type = 'Video' means this row should be tied to the videos table. Those comments are now orphans, permanently pointing to a non-existent video ID.

Over 18 months, your comments table accumulates thousands of orphans. Customer support reports "ghost comments" appearing on unrelated pages. The bug is intermittent because orphaned parent_id values occasionally collide with new IDs in different parent tables.

Querying all comments for a post requires filtering on two columns: WHERE parent_id = ? AND parent_type = 'Post'. Joining comments to their parent is not possible with a standard SQL JOIN. You need a UNION across three separate queries or a CASE statement in your ORM.

The performance cost is real too. An index on parent_id alone is often insufficient because it matches rows across all parent types. You need a composite index on (parent_type, parent_id), and even then, the index is less direct than a proper FK index on a single-type column.

Here's how index efficiency compares across 10 million comments:

The performance gap can widen with more parent types because the composite index has more distinct parent_type values to filter through.

How It Develops

Polymorphic associations emerge from reasonable instincts:

1. "One table is simpler than three." Nobody wants to create post_comments, video_comments, and product_comments. That feels like unnecessary duplication.
2. "Our ORM supports it." Rails' belongs_to :parent, polymorphic: true and Django's GenericForeignKey make this pattern a one-liner. If the framework supports it, it must be fine, right?
3. "We might add more parent types later." Adding a new commentable type with polymorphic associations means zero schema changes. With separate FK tables, you need a new table and migration.
4. "The prototype worked." With 1,000 comments and 3 parent types, everything is fast and no orphans exist yet. The problems emerge at scale and over time.

The core mistake: treating the database as a dumb storage layer and pushing integrity enforcement to the application. The database exists to enforce constraints. When you take that away, bugs become more likely and harder to detect as the system grows.

A common growth pattern is for the parent_type column to reach 14 distinct values: fourteen tables that can be the "parent" of an activity-log row. If even some of those types lack application-level cascade logic, the orphan count can become substantial before it is noticed.