Hierarchical/Nested Database Structure for Comments
- by Stephen Melrose
Hi,
I'm trying to figure out the best approach for a database schema for comments. The problem I'm having is that the comments system will need to allow nested/hierarchical comments, and I'm not sure how to design this out properly.
My requirements are,
Comments can be made on comments, so I need to store the tree hierarchy
I need to be able to query the comments in the tree hierarchy order, but efficiently, preferably in a fast single query, but I don't know if this is possible
I'd need to make some wierd queries, e.g. pull out the latest 5 root comments, and a maximum of 3 children for each one of those
I read an article on the MySQL website on this very subject,
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
The "Nested Set Model" in theory sounds like it will do what I need, except I'm worried about querying the thing, and also inserting. If this is the right approach,
How would I do my 3rd requirement above?
If I have 2000 comments, and I add a new sub-comment on the first comment, that will be a LOT of updating to do. This doesn't seem right to me?
Or is there a better approach for the type of data I'm wanting to store and query?
Thank you