How to Design DB For Nested comments?

인턴 과제 기간 동안 실질적으로 가장 고민한 영역으로

1. 진척율 Logic 처리
2. DB 설계

위 두 가지를 꼽을 수 있을 것 같다.

1번이 난항을 겪은 이유는 진척율 측정 방식에 대한 명확한 이해가 부족했고, 그걸 사용자마다 가중치를 두어 어떻게 진척율을 자동적으로 측정할 수 있도록 할 수 있을지 많이 고민했다.

2번은 무엇보다도 데이터베이스가 낯설게 느껴졌고, 어떻게 짜야 효율적으로 DB를 짤 수 있을지 주말마다 고민하였고 결국엔 기존의 데이터베이스 설계를 참고하기로 하였다ㅋㅋㅋ.ㅋ.ㅋㅋ.ㅋ...ㅋㅋ...

참고한 글의 링크는 바로 여기다!
친절하게 사진과 함께 DB가 업그레이드되는 것을 보여준다.

LEVEL 1.

• A user creates a post.
• A user comments on a post.

LEVEL 2.

• LEVEL 1 + A user comments(sub comment) on a comment
• So add attribute to parent_comment_id as a foreign key to Comment Table
• Query the comments on a post "1111" by each level

SELECT 
    post_id,
    parent.comment as level1_comment,
    child.comment as level2_comment
FROM
    Comment parent, Comment child
WHERE
    post_id = '1111' and
    child.parent_comment_id = parent.comment_id
	

• 하지만 level2까지만 적용된다는 단점이 존재한다.

LEVEL 3.

• If we have n levels nested comments?
• Does we have to self join the data n times?

Approach 1 - Repeated Self-joins(OMG)

SELECT 
    post_id,
    p1.comment as level1_comment,
    p2.comment as level2_comment,
    p3.comment as level3_comment,
    p4.comment as level4_comment
FROM
   Comment p1
LEFT JOIN
   Comment p2 on p2.parent_comment_id = p1.comment_id
LEFT JOIN
   Comment p2 on p3.parent_comment_id = p2.comment_id
LEFT JOIN
   Comment p2 on p4.parent_comment_id = p3.comment_id
WHERE
   post_id = '1111'

Approach 2 - Recursive CTE(Common Table Expression)

• It just simplifies the query syntax and data representation, it by no means

WITH RECURSIVE cte AS
    (SELECT
   	    comment,
        comment_id AS path,
        user_id,
        post_id
     FROM
        Comment
     WHERE parent_comment_id IS NULL
     UNION ALL
     SELECT
        comment,
        CONCAT(parent.path,'/',child.name) comment_id AS comment_id,
        user_id,
        post_id
     FROM
        Comment parent, Comment child
     WHERE
        child.parent_comment_id = parent.comment_id)
        
SELECT * FROM cte;

---

Alternative Data Model - Path-style Identifiers

• Remove self reference
• And add a new column - path
  
• You can now query parent comments by comparing the current row's path to a pattern formed from the path of another row.
• Find ancestors of comment #5, whose path is 2/3/4/5

SELECT *
FROM
    Comment AS c
WHERE (SELECT
        path
       FROM
        Comment
       WHERE
        comment_id = 5) LIKE c.path || '%';

• LIKE c.path || '%' = 뒤가 어찌 됐든 반드시 c.path로 시작해야한다.
• Drawbacks :

1. The database can't enforce that the path is formed correctly or that the values in the path correspond to existing comments.
2. VARCHAR column has a limit, and the depth of your path will be limited to this limit.

Closure Tables

• A simple and elegant way of storing bierarchies.
• Instead of using the comment table to store information abou the parent-child relationship.
• Let's create a lookup table "parent_child_comment"
  
• All the child comments for comment #3 will be as follows

SELECT *
FROM 
    Comment AS c![](https://velog.velcdn.com/images%2Fdleunji%2Fpost%2Fb09c7ea6-61b6-4b4d-bb02-d4e515335b59%2Fimage.png)![](https://velog.velcdn.com/images%2Fdleunji%2Fpost%2F5998970e-f326-4ae8-a78c-07e25579a18a%2Fimage.png)
JOIN parent_child_comment p 
ON c.comment_id = p.child_comment_id
WHERE
    p.parent_comment_id = 3;

이제 첨부파일 부분만 더 고민하면 된다!