Introduction

Comments and likes look simple in the interface, but the database design underneath matters a lot. A good schema has to support fast reads, prevent duplicate likes, and handle deletion rules cleanly as posts and comments change over time.

Start with a Clear Comment Model

A comments table usually belongs to a parent entity such as a post, article, or photo. If you want threaded replies, add a nullable parent comment reference.

1CREATE TABLE comments (
2    id BIGSERIAL PRIMARY KEY,
3    post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
4    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
5    parent_comment_id BIGINT REFERENCES comments(id) ON DELETE CASCADE,
6    body TEXT NOT NULL,
7    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
8);
9
10CREATE INDEX idx_comments_post_created
11    ON comments (post_id, created_at DESC);
12
13CREATE INDEX idx_comments_parent
14    ON comments (parent_comment_id);

This schema supports both top-level comments and replies. A top-level comment has parent_comment_id set to NULL. A reply points to another row in the same table.

Model Likes as a Relationship, Not a Counter Alone

A like is usually a many-to-many relationship between users and target records. The most important rule is that one user should only be able to like the same target once.

Separate tables keep referential integrity simple:

1CREATE TABLE post_likes (
2    post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
3    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
4    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
5    PRIMARY KEY (post_id, user_id)
6);
7
8CREATE TABLE comment_likes (
9    comment_id BIGINT NOT NULL REFERENCES comments(id) ON DELETE CASCADE,
10    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
11    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
12    PRIMARY KEY (comment_id, user_id)
13);

The composite primary key prevents duplicate likes automatically. If a second insert tries to like the same post again, the database rejects it.

Separate Tables vs One Polymorphic Likes Table

Some systems use one table with columns such as target_type and target_id. That can work, but it weakens referential integrity because one foreign key cannot point to both posts and comments cleanly in a standard relational design.

Separate tables are often easier to reason about:

• foreign keys stay real
• cascade delete behavior is straightforward
• indexes are simpler and smaller

A polymorphic table can make sense if you support many likeable entities, but it usually needs extra application logic or triggers to maintain integrity.

Querying Counts Efficiently

The interface often needs comment counts and like counts. You can compute them on demand:

1SELECT p.id,
2       COUNT(DISTINCT c.id) AS comment_count,
3       COUNT(DISTINCT pl.user_id) AS like_count
4FROM posts p
5LEFT JOIN comments c ON c.post_id = p.id
6LEFT JOIN post_likes pl ON pl.post_id = p.id
7WHERE p.id = 42
8GROUP BY p.id;

That works, but high-traffic applications often denormalize counts into columns such as posts.comment_count or posts.like_count. If you do that, keep the source-of-truth relationship tables anyway and update counters transactionally or through background jobs.

Think About Delete Behavior Early

Deletion rules affect user experience and data consistency. ON DELETE CASCADE is convenient when removing a post should automatically remove its comments and likes. However, some products prefer soft deletes so moderation history can be preserved.

That choice changes query logic, because soft-deleted comments should usually disappear from the interface while still existing in the database.

A Typical Insert Flow

Comment creation is a simple insert:

INSERT INTO comments (post_id, user_id, body)
VALUES (42, 7, 'Helpful explanation, thanks.');

A like can use an upsert-style pattern to avoid duplicate errors surfacing to the user:

INSERT INTO post_likes (post_id, user_id)
VALUES (42, 7)
ON CONFLICT DO NOTHING;

That gives you an idempotent "like" operation, which is helpful when clients retry requests.

Common Pitfalls

The most common design mistake is storing only a like counter and not the individual relationships. Without the user-to-target rows, you cannot prevent duplicate likes or answer who liked what.

Another issue is missing composite uniqueness. If the database does not enforce one-like-per-user-per-target, duplicate likes eventually appear.

Deeply nested comments can also become expensive to query. If you need unlimited threading, plan the retrieval strategy early instead of assuming a single self-reference solves the whole problem.

Summary

• Use a comments table with a self-reference when you need replies.
• Store likes as relationship rows, not only as counters.
• Enforce uniqueness with composite primary keys or unique constraints.
• Prefer separate like tables when you want strong foreign-key integrity.
• Add the right indexes and decide early between hard deletes, soft deletes, and denormalized counters.