Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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!

share|improve this question
Have you already designed tables for everything else? – FrustratedWithFormsDesigner Apr 18 '12 at 20:17

4 Answers

up vote 2 down vote

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.

http://blog.mclaughlinsoftware.com/2009/04/03/t-sql-hierarchical-query/

http://karwin.blogspot.ca/2010/03/rendering-trees-with-closure-tables.html

share|improve this answer
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). – Michael Durrant Apr 20 '12 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 – Aaron Brown Apr 21 '12 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? – Neil McGuigan Apr 21 '12 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 Apr 21 '12 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. – Aaron Brown Apr 22 '12 at 1:10
show 2 more comments
up vote 0 down vote

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

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

users
user_id # Primary key
name, etc.

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

comments
comment_id
post_id # Primary key
user_id # Who posted this comment.
#various comment fields - contents, etc.
share|improve this answer
up vote -1 down vote

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

share|improve this answer
up vote -1 down vote

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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