How to Limit Comment Nesting Depth in Self-Referencing Tables

#sql #database #architecture

When building a comment system (like Reddit or a tech blog), we often use a Self-Referencing Relationship. A comment entity references another comment as its parent.

While this allows for infinite nesting, infinite recursion is often bad for UI design and readability. A common requirement is to limit the nesting depth (e.g., max 5 levels).

Here is how to effectively handle this constraint in your database and application logic.

The Problem: Infinite Recursion

In a standard "Adjacency List" model, the table looks like this:

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

To limit the depth here, you would normally have to execute a recursive query (using CTEs in SQL) every time a user tries to post a reply, just to calculate the current depth. This is expensive and impacts write performance.

The Solution: Add a depth Column

The most efficient way to enforce a limit is to denormalize slightly by storing the depth explicitly in the table.

Database Schema Update

Add a depth (or level) integer column to your table.

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 don't need a recursive query. You simply check the parent's depth.

Logic:

Fetch the Parent Comment.
Check: if (parent.depth >= MAX_DEPTH) throw Exception.
Set Child Depth: child.depth = parent.depth + 1.

✅ Best Practice Code (Spring Boot / Java)
Here is how you can implement this in your Service layer.


@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);
    }
}

Why this approach?

Read Performance You don't need complex Recursive CTEs just to find out how deep a comment is. You can simply query WHERE depth < 5 if you want to filter deeply nested comments.
Write Performance Validating the constraint happens in O(1) time (constant time). You only need the parent's object, which you likely already have loaded to map the relationship.
UI Friendly Frontend developers love this. You can send the depth field in your API response, making it easy for them to indent the comments properly (e.g., margin-left: depth * 20px).