Portfolio_Site/content/posts/polymorphic-table-psql.md

title, date, draft, tags, description

title	date	draft	tags	description
The Likes Table Problem: Why We Went Polymorphic.	2026-04-01T21:50:00+05:45	false	architecture backend PSQL	Concept of polymorphic table in an SQL database

A few days ago, I was working on adding a Community section to an application. The idea was simple, users should be able to:

• Create posts
• Leave comments
• Like posts
• Like comments

We also had a separate News section. The new requirement was users should be able to like news articles as well.

Building model for posts and comments was pretty straight forward. The real challenge was to model thelikestable.

The Problem: How Do We Store Likes?

We’re using PostgreSQL, so enforcing relationships with foreign keys is easy and clean.

If only one thing could be liked (say, News), the schema would be simple, we would have a news_like table which could look something like this:

user_id	news_id	timestamp
(foreign key)	(foreign key)	1775059642

But we didn’t have one entity. We had three. posts, comments and news.

We had to decide:

Do we create three separate like tables? Or do we design one flexible solution?

Option 1: Three Separate Tables

We could create three tables: post_likes , comment_likes and news_likes . Each table would have proper foreign key relationships. This approach would be a clean relational way of doing things. It:

• Keeps strong relational integrity
• Makes joins easy
• Keeps structure explicit

This is the most “pure relational” approach. But it felt repetitive. The schema grows horizontally.

And if tomorrow we add something else that can be liked, we’d need yet another table.

It works, but it doesn’t scale elegantly.

Option 2: A Polymorphic Table (What We Chose)

Instead of multiple tables, we created a single polymorphic likes table.

What is a Polymorphic Table?

A polymorphic table can reference multiple types of resources using a shared structure.

We designed our likes table to look something like this:

user_id	resource_id	resource_type	timestamp
(foreign key)	(uuid)	(POST / COMMENT / NEWS)

Here’s how it works:

• user_id: who liked, foreign key to users table.
• resource_id : the UUID of the item, just the uuid, no foreign key relation.
• resource_type : what type of item it is
• timestamp : timestamp

Instead of a strict foreign key to one table, we store:

• The ID
• The type of resource

Together, they uniquely identify what was liked.

With this approach, we had one clean and centralized table to store all kinds of likes. It’s much easier to expand and flexible. Since “like” is a feature common to many parts of the system, this design keeps it generic and reusable.

But it does has a major downside. We lose direct foreign key enforcement on resource_id. Because PostgreSQL can’t enforce a foreign key that dynamically points to multiple tables, referential integrity must be handled at the application level. We cannot write a simple join query to join from comments table or posts table.

For example, to fetch likes for a resource:

SELECT COUNT(*)
FROM likes
WHERE resource_id = 'some-uuid'
AND resource_type = 'POST';

Now, if we need resource details plus likes, we may need separate queries or application-side logic.

For our use case though, that trade-off was acceptable. We don’t perform heavy cross-entity joins on likes, so the downside was minimal.

Why This Design Felt Right

The key insight was this:

“Like” is not tightly coupled to Posts, Comments, or News. It’s a behavior shared across resources.

By modeling it polymorphically, we treated “like” as a reusable system capability rather than a feature embedded in each entity.

And as our application grows, this decision will likely save us refactoring time.