How to Limit Comment Nesting Depth in Self-Referencing Tables

Source: Dev.to

Introduction

When building a comment system (e.g., Reddit or a tech blog) that uses a self‑referencing relationship, each comment can reference another comment as its parent. While this allows infinite nesting, deep recursion hurts UI readability and performance. A common requirement is to limit nesting depth (e.g., max 5 levels).

Standard Adjacency List Model

CREATE TABLE comment (
    id BIGINT PRIMARY KEY,
    parent_id BIGINT, -- References id
    content TEXT
);

Enforcing a depth limit with this schema would require a recursive CTE on every insert, which is expensive.

Database Schema Update

Add a depth (or level) column to store the nesting level explicitly.

CREATE TABLE comment (
    id BIGINT PRIMARY KEY,
    parent_id BIGINT,
    content TEXT,
    depth INT DEFAULT 1 -- 1 for root comments
);

Application Logic (JPA Example)

When creating a new comment, you only need to check the parent’s depth.

Steps

1. Fetch the parent comment.
2. Validate depth: if parent.depth >= MAX_DEPTH, reject the reply.
3. Set child depth: child.depth = parent.depth + 1.

Best‑Practice Code (Spring Boot / Java)

@Service
@RequiredArgsConstructor
public class CommentService {

    private final CommentRepository commentRepository;
    private static final int MAX_DEPTH = 5;

    @Transactional
    public void addReply(Long parentId, String content) {
        // 1. Fetch Parent
        Comment parent = commentRepository.findById(parentId)
            .orElseThrow(() -> new EntityNotFoundException("Parent not found"));

        // 2. Validate Depth
        if (parent.getDepth() >= MAX_DEPTH) {
            throw new IllegalArgumentException(
                "Comments cannot be nested deeper than " + MAX_DEPTH + " levels.");
        }

        // 3. Create Child
        Comment reply = Comment.builder()
            .content(content)
            .parent(parent)
            .depth(parent.getDepth() + 1) // Calculate depth immediately
            .build();

        commentRepository.save(reply);
    }
}

Read Performance

Since the depth is stored, you can filter comments without recursion, e.g.:

SELECT * FROM comment WHERE depth < 5;

Write Performance

Depth validation is O(1): you only need the parent record, which is typically already loaded.

UI Friendly

Expose the depth field in API responses. Front‑end code can then indent comments easily, e.g., margin-left: depth * 20px.

Conclusion

Storing a simple depth integer column provides:

• Immediate validation logic.
• Faster read/write queries.
• Cleaner UI handling.

Avoid recursive queries on inserts; rely on the denormalized depth column instead.