Advice on Structuring a Comment System

I am a beginner at database design and I would like to create a comment system with the ability to reply to user comments and to display potentially all comments a user has made. Furthermore, there will be many pages that will each have a section for commenting.

So far I have come up with two potential database designs to structure the comment system. The first will have a table for each pages' comments and a table for each users' comments. The page comment table will have user_id and page_id fields for table linking purposes.

The second potential design structure is to have one large partitioned table of comments that just has comment_id and user_id fields for table linking. I haven't thought about how to approach the reply feature yet; I wanted to get开发者_运维知识库 some input on which design approach, if any, would perform efficiently before I tackled that problem.

---

I'd go with two tables: one for comment threads and another for the comments. The comment threads would look something like this:

id
/* Other bookkeeping things like created time and such */

and comments:

id
thread_id
user_id
comment
parent_id
/* Other bookkeeping stuff */

Then attach the thread to the page by adding a comment_thread_id column to the page table.

Having a separate distinct comment thread gives you a convenient place to attach access control or similar extensions in the future, it also allows you to attach comment threads to things. Attaching the comment threads to the page rather than the other way around makes it easy to add comment threads to other objects in your system later on.

---

comments
   id
   user_id
   comment_text
   page_id
   parent_id

Use this to store comment for a page. parent_id is a comment that is a reply of another comment. For a comment on the page the parent_id will be zero (no parent comment), but for a reply the parent_id will be non-zero and it will be the ID of the comment which the reply belongs to.

For example, you will have data like this

id user_id comment_text page_id parent_id
1  6         sdh          1       0          <-- comment on the page
2  9         gfdf         2       0          <-- another comment on other page
3  4         reply xzy    1       1          <-- reply to comment id 1 by user id 4 on page 1

You may consider having timestamp, flagged, deleted etc fields that can help you in sorting, ordering, filtering, monitoring and soft-deleting a comment. You can group by page, by user, by parent.