Posted inDatabase

Designing Polymorphic Relationships in SQL: 3 Architectures from Production Experience

Database tutorial - IT technology blog
Database tutorial - IT technology blog

Solving the “One Entity, Many Subjects” Problem

Suppose you are building a comment feature for a social network. Users can comment on Posts, Videos, or Products. The naive approach is to create three tables like post_comments, video_comments… or cram three Foreign Key columns into a single table. Both methods lead to database bloat and are extremely hard to maintain as the system scales.

Polymorphic relationships are the lifesaver here. They allow a table to link flexibly to multiple other tables through a single relationship. Popular frameworks like Laravel or Rails often use a pair of ID and Type columns by default to handle this.

-- Quick implementation (suitable for startups)
CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    commentable_id INT NOT NULL, -- ID of Post, Video, or Product
    commentable_type VARCHAR(50) NOT NULL -- Stores values like 'Post', 'Video'...
);

Querying the data is quite straightforward:

SELECT * FROM comments WHERE commentable_type = 'Post' AND commentable_id = 10;

It only takes 5 minutes to design. However, if the system reaches 1 million records, this structure will reveal fatal weaknesses in performance and data integrity.

3 Common Polymorphic Design Methods

Based on various real-world CMS projects, I’ve found that no single architecture is the best. The choice depends on whether you prioritize development speed or data safety.

1. Polymorphic Association (ID & Type Pair)

This is the most flexible approach. You can add any new entity (like Photo or Album) without modifying the comments table structure.

  • Pros: Super-fast implementation, very clean code at the Application layer.
  • Cons: Cannot create Foreign Key constraints. The database cannot guarantee that commentable_id actually exists. If you delete a Post, you must manually write code to delete its comments to avoid orphaned data.

2. Exclusive Belongs To (Multiple Nullable Foreign Keys)

Instead of using a generic ID column, we create separate Foreign Key columns for each entity type.

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    content TEXT,
    post_id INT REFERENCES posts(id) ON DELETE CASCADE,
    video_id INT REFERENCES videos(id) ON DELETE CASCADE,
    CHECK (
        (post_id IS NOT NULL)::int + (video_id IS NOT NULL)::int = 1
    )
);
  • Pros: Leverages the power of Foreign Keys and the ON DELETE CASCADE feature. Query performance is extremely high thanks to standard SQL indexing.
  • Cons: The table can become cluttered if there are too many entities. Every time you add a new content type, you are forced to ALTER TABLE to add a column.

3. Class Table Inheritance (Intermediate Base Table)

This is the most formal and normalized approach. You create a common “interface” table to manage IDs.

-- Common identifier table
CREATE TABLE commentable_entities (id SERIAL PRIMARY KEY);

-- Post inherits ID from the table above
CREATE TABLE posts (
    id INT PRIMARY KEY REFERENCES commentable_entities(id),
    title VARCHAR(255)
);

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    entity_id INT REFERENCES commentable_entities(id),
    content TEXT
);

This method transforms the polymorphic relationship into a traditional 1-N relationship. Data remains extremely clean and transparent.

Index and Query Optimization Techniques

The most common mistake when using Method 1 (ID & Type) is indexing only the id column. When the dataset grows, the database will have to perform a Full Table Scan to filter by type.

Solution: Always use a Composite Index.

CREATE INDEX idx_comments_type_id ON comments (commentable_type, commentable_id);

In practice, the type column usually has low cardinality (few distinct values), so it should be placed first. This helps the database narrow down the search range significantly faster.

Sometimes you need to process data from CSV files to import into a new polymorphic structure. Instead of writing complex Python scripts, I often use toolcraft.app/en/tools/data/csv-to-json to quickly convert to JSON right in the browser. This tool processes data locally, making it quite safe for project data.

Eliminating N+1 Query Issues

An N+1 error occurs when you fetch 20 comments but end up running 20 additional queries to fetch the parent post titles. With polymorphic relationships, this error is even more dangerous because the data resides in different tables.

Use ORM Eager Loading or UNION ALL if writing raw SQL:

(SELECT c.*, p.title as parent_name FROM comments c 
 JOIN posts p ON c.commentable_id = p.id 
 WHERE c.commentable_type = 'Post' LIMIT 10)
UNION ALL
(SELECT c.*, v.name as parent_name FROM comments c 
 JOIN videos v ON c.commentable_id = v.id 
 WHERE c.commentable_type = 'Video' LIMIT 10);

Advice from Real-World Experience

After “paying the price” multiple times by cleaning up orphaned data, I have a few rules for you:

  • Startup projects needing speed: Prioritize ID & Type. Don’t obsess over Foreign Keys in the early stages, but you must create composite indexes from the start.
  • Financial or ERP systems: Base Table is mandatory. Data inconsistency is unacceptable here.
  • Memory optimization: Use VARCHAR(30) or ENUM for the type column instead of TEXT.
  • If using PostgreSQL: Try combining it with JSONB to store specific attributes for each polymorphic entity type.

Database design is a series of trade-offs. Carefully weigh flexibility against safety before you start writing CREATE TABLE commands.

Share:
Tags: