CODEWITHSUNDEEP
mysqlsqlmongodbnosql
mvbl fst
mvbl fst

Reputation: 5263

Knowing all that you know about SQL and no-SQL, what's best to store trees?

We're about to upgrade commenting system on our web site, which right now is a single-level structure and is using MySQL. We want change it to multi-level.

One thing to keep in mind is that we show all comments under each user profile.

So far considerations are as follows:

SQL pros:

SQL cons:

no-SQL pros:

no-SQL cons:

What are other pros and cons, in your production experience?

Upvotes: 0

Views: 153

Answers (4)

Steve Casey
Steve Casey

Reputation: 10156

for NOSQL (& can apply to SQL too), you do not have to store the data in a normalised form, denormalise it. store comments in two places! have a list of comments on the user.

some good reading from kyle banker on nested comment approaches.

and a similiar stackoverflow post

Upvotes: 0

Omar Qureshi
Omar Qureshi

Reputation: 9093

You can use a tree based structure in SQL, see LTree for PostgreSQL - http://www.postgresql.org/docs/9.1/static/ltree.html

On top of this, you can perform recursive queries using common table expressions like Disqus does - http://justcramer.com/2010/05/30/scaling-threaded-comments-on-django-at-disqus/

Upvotes: 1

AdaTheDev
AdaTheDev

Reputation: 147224

With RDBMS you can use JOINs - so store each comment just once, no data duplication (pro), and can fulfil both your display use cases by utilising JOINs.

With e.g. MongoDB, JOINs aren't supported (con), but you could achieve good performance for both cases by duplicating the data - store a comment once in the page document it relates to in the tree structure and once again in a "UserComments" collection document that relates to that specific user. i.e. to see all the comments associated with a page, query CollectionA for that page, to view all the comments for a user, query CollectionB by user id.

Upvotes: 1

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112299

You could also store all comments in a thread in a single record with SQL by storing it as an XML string. This would give you the easiness of access combined with fast retrieval.

Upvotes: 0

Related Questions