How would you model posts, comments, and threaded chat replies in a relational database?

[Moyosof]

Select Topic Area

Question

Body

Hey everyone,

I’d love to initiate a discussion on how developers approach designing relational database schemas for posts, comments, and threaded replies (such as chat or discussion threads).

The kind of structure I’m thinking of is similar to what you’d see in forums, comment sections, or collaborative tools:

- A Post starts the conversation.
- Comments can be added to the post.
- Then people can reply to individual comments — creating threaded discussions that may go multiple levels deep.

Some points to kick off the discussion:

1. Schema Structure

   • Would you go with a Comments table that references itself (ParentCommentId)?
   • Or separate the Comments and Replies tables?

2. Threading Depth

   • How do you handle deep nesting (e.g., reply-to-a-reply-to-a-reply)?
   • Do you cap the depth or flatten after a certain level?

3. Querying and Performance

   • How do you efficiently fetch and render comment threads with nested replies?
   • Any experiences using CTEs (Common Table Expressions) or app-side tree builders?

4. Soft Deletes / Moderation

   • How do you handle deletions without breaking the thread structure?
   • What about anonymous or deleted users in the context of old comments?

5. UX Considerations

   • Have you ever made changes to your DB design based on how the UI needed to work?
   • What’s worked best when displaying threads: flat + indent, actual trees, etc.?

This is something that comes up in lots of apps, from blogs and forums to support systems and chat platforms, and there’s no one-size-fits-all. I’m curious to hear how different people have approached this problem, especially with scalability, maintainability, and developer experience in mind.

Feel free to share your models, thoughts, trade-offs, or even pain points from past projects.

Looking forward to learning from the community!

[CollatzConjecture]

Single self-referencing table all the way; Don't overthink this, it's a solved pattern.

CREATE TABLE comments (
  id BIGINT PRIMARY KEY,
  post_id BIGINT NOT NULL,
  parent_id BIGINT NULL, -- references comments.id
  user_id BIGINT NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMP,
  deleted_at TIMESTAMP NULL, -- soft delete
  path VARCHAR(255) -- materialized path like "1.5.12"
);

-- Essential indexes
CREATE INDEX idx_comments_post_parent ON comments(post_id, parent_id);
CREATE INDEX idx_comments_path ON comments(post_id, path);

Key decisions:

1. Threading: Cap at 3-4 levels max. Beyond that, UX becomes terrible anyway. Either flatten or show "continue thread" links.
2. Querying: Materialized path wins over recursive CTEs for read-heavy workloads. Update the path on insert/move - it's worth the write overhead.
3. Soft deletes: Keep the record, null out content/user_id, show "[deleted]". Never break the tree structure.
4. Fetching: Single query with ORDER BY path gives you everything in render order. No N+1 queries, no complex recursion.

WHERE post_id = ? AND deleted_at IS NULL 
ORDER BY path;

Performance reality check: Reddit, HN, and most forums use variations of this. If it's good enough for millions of comments, it's good enough for your app.
The separate Comments/Replies table approach is just unnecessary complexity. You'll end up duplicating fields and creating join hell for no benefit.
UX tip: Most users don't engage beyond 2-3 reply levels anyway. Design for the 90% case, not the edge cases.

[Moyosof]

This is great, but I would like to ask follow-up question, if you don't mind
How do you manage updates to the path column if a comment is moved or deleted? Do you use triggers or application-side logic to keep paths consistent?

[CollatzConjecture]

This is great, but I would like to ask follow-up question, if you don't mind How do you manage updates to the path column if a comment is moved or deleted? Do you use triggers or application-side logic to keep paths consistent?

Application-side logic, not triggers, triggers are debugging nightmares and harder to test
For moves (rare in most apps):

-- Update the moved comment's path
UPDATE comments SET path = ? WHERE id = ?;

-- Update all descendants 
UPDATE comments 
SET path = REPLACE(path, old_prefix, new_prefix)
WHERE path LIKE 'old_prefix.%';

For deletes: don't move anything, just soft delete and leave paths intact:

UPDATE comments 
SET deleted_at = NOW(), content = NULL 
WHERE id = ?;

The tree structure stays consistent, you just hide deleted nodes in queries.

Comment moves are extremely rare in most apps. Reddit doesn't allow it, HN doesn't allow it. If you're building a forum where mods frequently reorganize threads, then yeah, you need the move logic.

But for 95% of use cases, soft delete + immutable paths is way simpler and more reliable.

Maybe another tip: If you absolutely need moves, batch the updates in a transaction and invalidate any cached thread data for that post.

[Julian-Dumitrascu]

As you started this conversation here, I thought you'd like to imitate message threading in GitHub, as they thread messages in only one tier only in "discussions". (They could let us enable in repositories any feature of "discussions".) I read "table" and thought "project table".
If you're building software with such threading, we can communicate about it in a repository. I can pay attention at least as a potential user.