Modeling Polymorphic Relationships for Web Apps: Patterns, Trade-offs, and Practical SQL

At some point in almost every serious web app, you need one thing to point at many different kinds of things. Comments that can live on tickets, tasks, or documents. Attachments that belong to users, organizations, or deals. Activity feeds referencing any entity. That is a polymorphic relationship—one table relating to multiple target tables—and it’s deceptively tricky to model well in a relational database.

This guide walks through the most common patterns for polymorphic relationships, where they shine, where they break, and how to implement them safely and performantly with Postgres and SQL. We’ll also cover multi-tenant constraints, indexing, migrations, and practical examples you can adapt today.

The problem in one minute

Relational databases love concrete foreign keys: a column references a specific table and key, with the database enforcing referential integrity. Polymorphism works against that grain by asking: “Can this row reference a row in any one of several tables?” RDBMSs don’t natively support a foreign key that points to multiple tables. You have to choose a pattern that balances integrity, flexibility, and performance.

Common use cases that need polymorphism

• Comments: A generic comments table where a comment can be added to a task, ticket, deal, file, or user profile.
• Attachments: Files or links that can be attached to many entity types.
• Activities: A timeline that references any object (e.g., “Alice updated Invoice #123”).
• Addresses or Contacts: Reusable records that might belong to either a person or an organization.
• Reactions, Likes, Votes: One interaction model applied across multiple content types.

Pattern 1: Type-and-ID columns (ad-hoc polymorphism)

This is the simplest and most common approach. You store a pair of columns such as subject_type and subject_id on the referencing table (e.g., comments). subject_type contains an enum or text value that indicates the target table. subject_id is the primary key value in that table.

-- Example: a comments table that can attach to tasks, tickets, or documents CREATE TYPE subject_type AS ENUM ('task', 'ticket', 'document'); CREATE TABLE comments ( id BIGSERIAL PRIMARY KEY, tenant_id BIGINT NOT NULL, author_id BIGINT NOT NULL REFERENCES users(id), body TEXT NOT NULL, subject_type subject_type NOT NULL, subject_id BIGINT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); -- Helpful composite index for lookups by subject CREATE INDEX idx_comments_subject ON comments(subject_type, subject_id, tenant_id); 

Pros:

• Very simple to create and query.
• No schema changes when you add a new subject type—just allow a new value in subject_type.

Cons:

• No native foreign key constraint to the target row. The database won’t stop you from referencing a non-existent record.
• No native ON DELETE CASCADE to clean up related rows.
• Harder to maintain referential integrity without triggers.

Mitigations:

• Use triggers to enforce existence and cascade deletes.
• Use application logic on writes to validate that the referenced record exists.
• Keep subject_type as a constrained enum and require tenant_id to scope references safely in multi-tenant systems.

-- Example: trigger to enforce that the referenced subject exists (Postgres) CREATE OR REPLACE FUNCTION check_comment_subject() RETURNS TRIGGER AS $$ BEGIN IF NEW.subject_type = 'task' THEN PERFORM 1 FROM tasks WHERE id = NEW.subject_id AND tenant_id = NEW.tenant_id; IF NOT FOUND THEN RAISE EXCEPTION 'Comment references missing task %', NEW.subject_id; END IF; ELSIF NEW.subject_type = 'ticket' THEN PERFORM 1 FROM tickets WHERE id = NEW.subject_id AND tenant_id = NEW.tenant_id; IF NOT FOUND THEN RAISE EXCEPTION 'Comment references missing ticket %', NEW.subject_id; END IF; ELSIF NEW.subject_type = 'document' THEN PERFORM 1 FROM documents WHERE id = NEW.subject_id AND tenant_id = NEW.tenant_id; IF NOT FOUND THEN RAISE EXCEPTION 'Comment references missing document %', NEW.subject_id; END IF; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trg_check_comment_subject BEFORE INSERT OR UPDATE ON comments FOR EACH ROW EXECUTE FUNCTION check_comment_subject(); 

When to use: Early-stage products, moderate data volumes, or when you need maximum flexibility and can tolerate application- or trigger-enforced integrity.

Pattern 2: Separate join tables per target type

Instead of a single subject_type/subject_id pair, you create one join table per target type with a concrete foreign key. For example, comment_tasks, comment_tickets, and comment_documents.

CREATE TABLE comments ( id BIGSERIAL PRIMARY KEY, tenant_id BIGINT NOT NULL, author_id BIGINT NOT NULL REFERENCES users(id), body TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); -- Per-target join tables with real FKs CREATE TABLE comment_tasks ( tenant_id BIGINT NOT NULL, comment_id BIGINT NOT NULL REFERENCES comments(id) ON DELETE CASCADE, task_id BIGINT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE, PRIMARY KEY (tenant_id, comment_id) ); CREATE TABLE comment_tickets ( tenant_id BIGINT NOT NULL, comment_id BIGINT NOT NULL REFERENCES comments(id) ON DELETE CASCADE, ticket_id BIGINT NOT NULL REFERENCES tickets(id) ON DELETE CASCADE, PRIMARY KEY (tenant_id, comment_id) ); CREATE TABLE comment_documents ( tenant_id BIGINT NOT NULL, comment_id BIGINT NOT NULL REFERENCES comments(id) ON DELETE CASCADE, document_id BIGINT NOT NULL REFERENCES documents(id) ON DELETE CASCADE, PRIMARY KEY (tenant_id, comment_id) ); 

Pros:

• True referential integrity with database-enforced foreign keys.
• Native cascading deletes and updates.
• Simple and fast joins for each target type.

Cons:

• More tables, more migrations when adding a new target type.
• Queries that need to union across all targets become verbose.

-- Fetch comments for any subject by unioning per-target tables SELECT c.* FROM comments c JOIN comment_tasks ct ON ct.comment_id = c.id AND ct.tenant_id = c.tenant_id WHERE ct.task_id = $1 AND c.tenant_id = $2 UNION ALL SELECT c.* FROM comments c JOIN comment_tickets ck ON ck.comment_id = c.id AND ck.tenant_id = c.tenant_id WHERE ck.ticket_id = $1 AND c.tenant_id = $2 UNION ALL SELECT c.* FROM comments c JOIN comment_documents cd ON cd.comment_id = c.id AND cd.tenant_id = c.tenant_id WHERE cd.document_id = $1 AND c.tenant_id = $2 ORDER BY created_at DESC; 

When to use: You need strict integrity and predictable performance, and your number of target types is relatively small and stable.

Pattern 3: A global entities table and an edges table

This pattern introduces a uniform identity for all entities, regardless of their concrete table. You create a entities table where each real row (task, ticket, document, etc.) has a corresponding entity_id. Polymorphic relationships then reference entities.id, not per-table primary keys.

-- One canonical identity for everything CREATE TABLE entities ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id BIGINT NOT NULL, kind TEXT NOT NULL, -- e.g., 'task', 'ticket', 'document' created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); -- Example concrete table has a 1-1 with entities CREATE TABLE tasks ( id BIGSERIAL PRIMARY KEY, tenant_id BIGINT NOT NULL, entity_id UUID NOT NULL UNIQUE REFERENCES entities(id) ON DELETE CASCADE, title TEXT NOT NULL ); CREATE TABLE comments ( id BIGSERIAL PRIMARY KEY, tenant_id BIGINT NOT NULL, author_id BIGINT NOT NULL REFERENCES users(id), body TEXT NOT NULL, subject_eid UUID NOT NULL REFERENCES entities(id) ON DELETE CASCADE, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX idx_comments_subject_eid ON comments(subject_eid, tenant_id); 

Pros:

• Single foreign key target with native referential integrity.
• No unions for cross-type queries; comments simply join to entities.
• Natural fit for activity feeds and cross-cutting features.

Cons:

• More moving parts. You must ensure every real row has an entity row.
• Migrations to backfill entities for existing data.
• Requires discipline to keep entities.kind and concrete tables in sync.

-- Insert a new task and its entity in a transaction BEGIN; entities (tenant_id, kind) VALUES ($1, 'task') RETURNING id; -- suppose we got :eid tasks (tenant_id, entity_id, title) VALUES ($1, :eid, $2); COMMIT; -- Query: load comments for a task by task.entity_id SELECT c.* FROM comments c JOIN tasks t ON t.entity_id = c.subject_eid AND t.tenant_id = c.tenant_id WHERE t.id = $1 AND t.tenant_id = $2 ORDER BY c.created_at DESC; 

When to use: You want strong integrity, a single relationship path for cross-cutting features, and you expect more entity types over time. This pattern scales cleanly in complex SaaS systems.

Pattern 4: Table inheritance and STI/CTI variants

Some teams consider single-table inheritance (STI) or class-table inheritance (CTI) to normalize relationships. For polymorphism, you might unify targets into a single super table with a type column (STI) or split shared and specific columns across a base and subtype tables (CTI).

Pros: Simpler joins if you can truly unify shapes. Database-level FKs become straightforward.

Cons: For many real-world domains, forcing dissimilar entities into one table leads to sparse columns, complex constraints, and tricky migrations. CTI adds joins and complexity. Most teams prefer Patterns 2 or 3 for polymorphism unless entities are naturally similar.

Choosing the right pattern

• Few target types, need strict integrity: Separate join tables (Pattern 2).
• Many target types, cross-cutting features, long-term scalability: Global entities and edges (Pattern 3).
• Early stage, speed over strictness: Type-and-ID columns with triggers (Pattern 1).
• Targets share nearly identical schemas: Consider STI/CTI (Pattern 4), but evaluate the maintenance cost.

Multi-tenant considerations

In multi-tenant apps, scope every relationship by tenant_id. This prevents cross-tenant leaks and improves index selectivity.

• Include tenant_id in composite keys and indexes: e.g., (tenant_id, subject_eid).
• Row Level Security (RLS): Add policies that restrict rows by tenant_id and the caller’s permissions. Apply RLS consistently to referencing and referenced tables.
• Validate tenant boundaries on write: Triggers or application checks should ensure that the tenant_id on both sides of the relationship match.

-- Example RLS policy (Postgres) ALTER TABLE comments ENABLE ROW LEVEL SECURITY; CREATE POLICY comments_tenant_policy ON comments USING (tenant_id = current_setting('app.tenant_id')::BIGINT) WITH CHECK (tenant_id = current_setting('app.tenant_id')::BIGINT); 

Indexes that actually help

Choose indexes based on real query shapes, not guesswork:

• Lookup by subject: CREATE INDEX ... ON comments(tenant_id, subject_type, subject_id) for Pattern 1.
• Lookup by entity: CREATE INDEX ... ON comments(tenant_id, subject_eid, created_at DESC) for Pattern 3.
• Count fast: For dashboards showing comment counts per item, consider a partial index or a materialized view refreshed on a schedule.

-- Fast count by subject for Pattern 3 CREATE INDEX idx_comments_subject_for_count ON comments(tenant_id, subject_eid); -- Example aggregation SELECT subject_eid, COUNT(*) FROM comments WHERE tenant_id = $1 AND subject_eid = ANY($2) GROUP BY subject_eid; 

Cascades, clean-up, and referential integrity

With Pattern 2 and Pattern 3, you get native ON DELETE CASCADE. With Pattern 1, you need to implement clean-up logic:

• Database triggers: On deletion of a task/ticket/document, delete rows in comments that reference the target.
• Background jobs: If synchronous deletes are too slow, queue clean-up jobs but be prepared for temporary dangling references.
• Soft deletes: If you soft-delete targets, decide whether related comments should remain visible. Add a deleted_at and filter at query time.

-- Trigger-based cleanup for Pattern 1 (example for tasks) CREATE OR REPLACE FUNCTION cleanup_comments_for_task() RETURNS TRIGGER AS $$ BEGIN DELETE FROM comments WHERE subject_type = 'task subject_id = OLD.id AND tenant_id = OLD.tenant_id; RETURN OLD; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trg_cleanup_comments_for_task AFTER DELETE ON tasks FOR EACH ROW EXECUTE FUNCTION cleanup_comments_for_task(); 

Query examples: building a comments feature

Insert a comment

-- Pattern 1 comments (tenant_id, author_id, body, subject_type, subject_id) VALUES ($1, $2, $3, 'task', $4) RETURNING *; -- Pattern 3 comments (tenant_id, author_id, body, subject_eid) VALUES ($1, $2, $3, $4) RETURNING *; 

List comments for a page

-- Pattern 1 SELECT * FROM comments WHERE tenant_id = $1 AND subject_type = 'ticket subject_id = $2 ORDER BY created_at DESC LIMIT 50; -- Pattern 3 SELECT c.* FROM comments c JOIN entities e ON e.id = c.subject_eid AND e.tenant_id = c.tenant_id WHERE e.kind = 'ticket e.tenant_id = $1 AND e.id = $2 ORDER BY c.created_at DESC LIMIT 50; 

Activity feed across many types

Pattern 3 excels here because everything shares a single identity. You can store activities with actor_eid, subject_eid, and context_eid (optional), then query feeds efficiently.

CREATE TABLE activities ( id BIGSERIAL PRIMARY KEY, tenant_id BIGINT NOT NULL, actor_eid UUID NOT NULL REFERENCES entities(id), subject_eid UUID NOT NULL REFERENCES entities(id), verb TEXT NOT NULL, -- 'created', 'updated', 'commented' metadata JSONB, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX idx_activities_subject ON activities(tenant_id, subject_eid, created_at DESC); 

ORM and tooling considerations

• Rails: Supports polymorphic associations with type/id columns (Pattern 1). You’ll still need custom cleanup or callbacks.
• Prisma/Sequelize/TypeORM: Typically favor Pattern 2 or Pattern 1. For Pattern 3, you’ll likely model entities explicitly and create relations to entity_id.
• Migrations: For Pattern 3, create entities, backfill by inserting a row for each existing record, and add entity_id to those tables in batches to avoid long locks.

Performance at scale

• Hot partitions: If many comments land on a single item, ensure your indexes support rapid inserts and reads (e.g., index on (tenant_id, subject_eid, created_at desc)). Consider fillfactor and autovacuum tuning.
• Pagination: Prefer keyset pagination over OFFSET as volumes grow. Combine with created_at and a tiebreaker ID.
• Caching: Cache counts and the first page of comments per subject. Invalidate on write. For Pattern 3, caching by subject_eid is straightforward.
• Archival: Move very old comments or activities to archival tables or partitions to keep primary indexes small.

Security and data hygiene

• Permissions: A comment referencing an entity does not imply the user may view that entity. Always check permissions on the target before returning related data.
• PII in cross-cutting tables: Comments and activities often mix sensitive data across contexts. Classify and redact as needed. Provide export/delete workflows for privacy requests when relevant to your product and jurisdiction.
• Input validation: Prevent spoofing by verifying that incoming references match allowed kinds and belong to the caller’s tenant.

Migrating from Pattern 1 to Pattern 3

1. Create entities: Add the entities table and backfill rows for each existing record type in batches (e.g., 10k at a time).
2. Add entity_id to concrete tables: Populate from the newly created entities rows.
3. Dual-write temporarily: On new inserts, write both subject_type/subject_id and subject_eid while you migrate readers.
4. Migrate readers: Update queries to use subject_eid, validate results and performance.
5. Remove legacy columns: Once stable, drop the old columns and triggers.

Testing strategies

• Integrity tests: Attempt to insert a comment with a bad reference; ensure it fails (via triggers or FKs).
• Tenant isolation: Attempt cross-tenant references; verify rejection.
• Deletion cascades: Delete a target subject and confirm related rows are removed or hidden according to your policy.
• Concurrency: Simulate burst writes to hot subjects; watch for deadlocks and tune indexes.
• Large subject pages: Benchmark list and count queries with realistic volumes.

Practical examples you can adapt

Attachments across many objects (Pattern 2)

CREATE TABLE attachments ( id BIGSERIAL PRIMARY KEY, tenant_id BIGINT NOT NULL, uploader_id BIGINT NOT NULL REFERENCES users(id), file_key TEXT NOT NULL, -- path/key in your storage provider filename TEXT NOT NULL, size_bytes BIGINT NOT NULL, mime_type TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); -- Join tables CREATE TABLE attachment_tasks ( tenant_id BIGINT NOT NULL, attachment_id BIGINT NOT NULL REFERENCES attachments(id) ON DELETE CASCADE, task_id BIGINT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE, PRIMARY KEY (tenant_id, attachment_id) ); -- Repeat for other types: tickets, documents, etc. 

This offers solid guarantees and simple cleanup, ideal for compliance-sensitive file management.

Activity feed with entities and edges (Pattern 3)

-- Edges let you express arbitrary relationships CREATE TABLE edges ( tenant_id BIGINT NOT NULL, src_eid UUID NOT NULL REFERENCES entities(id) ON DELETE CASCADE, dst_eid UUID NOT NULL REFERENCES entities(id) ON DELETE CASCADE, kind TEXT NOT NULL, -- 'assigned_to', 'related_to', etc. created_at TIMESTAMPTZ NOT NULL DEFAULT now(), PRIMARY KEY (tenant_id, src_eid, dst_eid, kind) ); 

With edges and activities both referencing entities, you can power graph-like features, show “related items,” and build flexible scopes for feeds without unions.

Using YeshBe to build these patterns

YeshBe is an AI-powered Vibe Coding platform for building software. Instead of starting from a blank codebase, you describe the data model, interfaces, and workflows in natural language and refine them through conversation. You still decide the patterns; the platform helps you implement them faster.

Here are example prompts you could use while building:

Set up a comments feature. Use a global entities table so that tasks, tickets, and documents each have an entity_id. Comments should reference entities via subject_eid, include tenant_id scoping, and support listing, creation, and deletion with permission checks.

Create database triggers that prevent cross-tenant references for comments and ensure subject_eid exists. Add indexes for fast pagination and counts.

Build an activity timeline component that shows entries from the activities table, grouped by day, for any entity_id. Include filters for actor, verb, and date range.

Because YeshBe is a platform for building software, you can adapt these patterns to your exact needs—comments today, attachments tomorrow—without being boxed into a one-size-fits-all system.

Pitfalls to avoid

• Dangling references: If you use Pattern 1, implement checks and clean-up rigorously. Don’t rely solely on app code paths; add triggers.
• Missing tenant constraints: Always include tenant_id in foreign keys and unique indexes for multi-tenant apps.
• Over-indexing: Indexes speed reads but slow writes. Focus on your hottest query shapes and prune unused indexes.
• Premature generalization: If you only need two target types and volumes are small, Pattern 2 might be simpler than adopting entities/edges too early.

Summary: a decision checklist

1. How many target types exist now, and how quickly will that grow?
2. Do you require database-enforced referential integrity?
3. Will you build cross-cutting features (feeds, search, permissions) that benefit from a uniform entity identity?
4. Are you multi-tenant? Have you embedded tenant_id into all keys and constraints?
5. What are the heaviest query shapes (lists, counts, joins)? Have you picked indexes accordingly?
6. What’s your deletion policy (hard vs soft)? Have you implemented cascades or clean-up jobs?
7. Do you have a safe migration path if your initial pattern proves too limiting?

Polymorphic relationships are a design choice, not a trick. With the right pattern for your stage and constraints, you can keep your schema coherent, your queries fast, and your product flexible as it evolves.