Hello, I'm trying to implement the comment function during the development of the bulletin board.
I'm confused no matter how much I think about it and search for it.
I'd like to ask for your help.
First of all, if I were to ask you that I designed the DB based on the results I looked for,
- Comments Table -
cmt_no: Comments No (pk)
content: content
parent: parent Comment No
depth: 1-comment, 2-comment
seq: order of comments
It's designed to be
a. Normal comments do not have a parent value.
b. Comments have parent comment No.
c. The first comment has a value of seq + 1 for parent.
d. The general comment is depth 1 and the comment is depth 2 and the comment cannot be written in the comment. (i.e., the depth value is 1 or 2)
The image below shows the db inquiry.
The image below shows the comments I actually saw on the bulletin board in order.
Question
1. Is the db structure for the comment I designed correct?
2. If it is correct, how should I make a query to inquire like the image below?
database main-comment schema comment
If you wrote the depth column to distinguish between comments and comments, you don't need the depth column. It can be determined by the nullity of the parent.
Looking at the results, comments (parent is null) are queried first if their cmt_no is smaller. If the parent's cmt_no is smaller, the comment should be viewed first.
Therefore,
SELECT *
FROM COMMENTS
ORDER BY IF(ISNULL(PARENT), CMT_NO, PARENT), SEQ;
You can look it up.
The image below is the result of turning the above sql on my computer mysql server.
© 2024 OneMinuteCode. All rights reserved.