Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

mysql - 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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...