Well, I need to create a comment system for posts that people add to my web app. This web app is just like facebook or myspace posts. Users add posts and people can comment on them. But the problem is I don't know how to store each comment in mysql table. How can I map each comment to the specific author.

I am new to mysql and how can I learn to solve things like that. Is there any books on the subject. I know some basic sql.

Thank you!


4 Answers 4


You could use Nested Set, but CRUD operations are more complex than other patterns, and there's no referential integrity.

The Closure Table pattern is simpler to CRUD and supports integrity, but requires an extra table.

Recursive Query is the simplest and supports integrity, but you would need a more advanced database like Postgres, SQL Server, or Oracle.



  • 1
    I won't downvote but I will say he doesn't need if for what he's doing. I've seen very complex apps with mysql without WITH (pun). Commented Apr 20, 2012 at 21:37
  • There are several design patterns for hierarchical data in RDBMSes. Most commonly, the Nested Set model is used for which there are plugins for most ORMs to simplify acccess. en.wikipedia.org/wiki/Nested_set_model Commented Apr 21, 2012 at 13:45
  • Why would you recommend Nested Set over Recursive Query or Closure Table when both are substantially simpler and support referential integrity and NS does not? Commented Apr 21, 2012 at 18:17
  • I would not recommend recursive query for the processing time when hierarchy get deeper it takes longer, meanwhile, I would recommend Closure table for its easiness (more over with dynamic hierarchy depth) and the processing time which with high depth hierarchy will be much faster.
    – Spredzy
    Commented Apr 21, 2012 at 19:51
  • I was simply recommending Nested Sets over a recursive query because the poster is using MySQL, and it is a well known model. I did not say it was the only option. Telling someone to change their database engine is not particularly helpful, nor necessary in this case. Commented Apr 22, 2012 at 1:10

There a fairly good set of Tutorials here: http://www.mysqltutorial.org/

I'm thinking of something like the following for you.

user_id # Primary key
name, etc.

post_id # Primary key
user_id # Who created the initial post.
#various post fields - contents, etc.

post_id # Primary key
user_id # Who posted this comment.
#various comment fields - contents, etc.

I guess that everybody is going to tell you to take a quick look to database design (E-R Model), create your model and we'll help you to adapt it to your web app: http://en.wikipedia.org/wiki/Entity-relationship_model you can go to youtube there is a bunch of good tutorial how to design a relational database


Fb comments allow you to comment on the orginal post so you just need a single table with a parent_id that defaults to 0. For all the posts, select where parent_id=0 and for the comments select where parent_id==post and sort by time

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.