Polymorphic Associations and Their Risks

Learning outcomes

A polymorphic association tries to let one row reference different target tables depending on a type field. It is common in application frameworks because it is convenient, but it weakens relational integrity when the database cannot express a normal foreign key.

The polymorphic foreign-key pattern

Comment(  comment_id,  subject_type,  -- 'WorkOrder', 'Asset', 'Customer'  subject_id,  body)

The application interprets subject_id according to subject_type.

Why the database cannot enforce it normally

A foreign key references one target relation:

FOREIGN KEY (subject_id) REFERENCES WorkOrder(work_order_id)

cannot sometimes reference Asset and sometimes Customer.

Orphans become possible

subject_type = 'Asset'subject_id = 999999

may refer to no Asset at all, and the database may not know.

Alternative 1: explicit nullable foreign keys

Comment(  comment_id,  work_order_id NULL,  asset_id NULL,  customer_id NULL,  body)

with a CHECK requiring exactly one target.

Pros and cons

Pros:

• real foreign keys;
• clear target tables;
• easy cascades/restrictions.

Cons:

• many nullable columns;
• schema changes when adding target types.

Alternative 2: separate association tables

WorkOrderComment(comment_id, work_order_id)AssetComment(comment_id, asset_id)CustomerComment(comment_id, customer_id)

This is verbose but highly explicit.

Alternative 3: supertype entity

Create a shared parent:

CommentSubject(  subject_id,  subject_kind)WorkOrder(  subject_id PK/FK -> CommentSubject,  ...)Asset(  subject_id PK/FK -> CommentSubject,  ...)Comment(  comment_id,  subject_id FK -> CommentSubject,  body)

Supertype benefits

• one real FK target;
• shared identity;
• common relationships attach cleanly;
• new subtypes can be added intentionally.

Supertype cost

It introduces an abstraction that must be semantically real. Do not invent a meaningless universal “Entity” table solely to make every FK generic.

Attachment example

Attachment(  attachment_id,  owner_type,  owner_id,  storage_key)

This is common, but the same integrity issue exists.

Explicit ownership alternative

If attachments only belong to WorkOrders and Assets, explicit FKs or association tables are often clearer than a universal type/id pair.

Generic audit events

Audit systems sometimes intentionally use:

entity_typeentity_idpayload_snapshot

because the event log must survive even if the source row is later deleted. In that case, absence of a live FK may be intentional rather than accidental.

Different lifecycle can justify loose references

If a historical event must remain after the target disappears, a strict FK may conflict with retention semantics. Preserve stable identifiers and snapshots instead, but document the reason.

Polymorphism through subtype tables

Party(  party_id,  party_type)Person(  party_id PK/FK -> Party)Organization(  party_id PK/FK -> Party)

Relationships can reference Party when both subtypes legitimately share the role.

WorkshopHub examples

ORM convenience versus database truth

An ORM feature can make polymorphic associations look easy in code. That does not change what constraints the database can enforce. Database design should not be dictated solely by framework convenience.

Practice: choose an alternative

Summary and next lesson

Polymorphic associations are convenient but often trade away foreign-key enforcement. Explicit FKs, association tables, or real supertypes preserve relational integrity. The next lesson examines another generic-schema pattern: Entity-Attribute-Value.

References

• Bill Karwin, SQL Antipatterns.
• Martin Fowler, patterns for inheritance and relational mapping.
• Ramez Elmasri and Shamkant B. Navathe, Fundamentals of Database Systems.