Design a Comment and Reply Data Model

A discussion system usually needs to represent a top-level comment, its replies, and the user being replied to.

A practical table can include:

id
content_id       -- post, article, or status being discussed
root_id          -- top-level comment ID
parent_id        -- direct parent reply
author_id
reply_to_user_id
body
status
created_at

Top-level comments may use root_id = id after insertion and a null parent_id. Replies share the same root_id, which makes it efficient to fetch one thread while preserving the direct parent relationship.

Index (content_id, status, created_at, id) for top-level pagination and (root_id, status, created_at, id) for replies. Prefer cursor pagination to deep offsets on active content.

Do not trust cached reply counts as the only source of truth. Update counters transactionally or reconcile them. Store moderation status rather than deleting immediately when auditability matters. Escape output, limit body size, rate-limit posting, enforce authorization, and avoid unbounded recursive queries or UI nesting.