mysql - Advice on Structuring a Comment System

Question

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.

Answer 1

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.