3

I have been developing a blogging site, where people can post an article or blog and others can comments on them. The site is coded in Angular.js, ASP.NET and SQL is being used a data store.

For each article there can be 'n' number of comments and anyone who is registered in the site can put any number of comments in for an article. Each comment can have any number of replies, and the replies can also have replies (so it's like a tree). So each comment will be like a tree and there shouldn't be any restrictions on the number of levels. And each article in turn will have a collection of such comment-trees.

I am expecting the application to be extremely read-heavy and moderately write-heavy. What can be a good data store for storing such data?

5

You're prematurely optimizing.

How much messages you'll display on a page? One hundred? One thousand? One billion?

If the number of messages is too much for Microsoft SQL Server, how would you expect any browser to show that many messages to the user, and how would you expect any user to be actually interested in seeing hundreds of thousands¹ of messages on a single page? What's the point?

When it comes to storing hierarchies, Microsoft SQL Server has hierarchical data feature. SQL Server also allows you to store XML, but I wouldn't advise you to use it in this specific case: SQL Server's XML fields are more for hierarchical data which doesn't change too much through time.

By the way, talking about hierarchical structures, you're not doing a service to your users. Tree structures are for developers; they are an extremely poor data visualization for things such as discussions, and should be avoided in this context. Flat structure of StackExchange, by opposition to old tree-oriented bulletin boards, is a good illustration of replacing an unusable approach by a very effective one.


¹ Actually, I imagine that Microsoft SQL Server would laugh at you if you tell him that you're scared to load hundreds of thousands of messages. If your table has proper indexes and foreign keys, the query will virtually take milliseconds.

  • "Flat structure of StackExchange, by opposition to old tree-oriented bulletin boards, is a good illustration of replacing an unusable approach by a very effective one." - that's an opinion only. You're prematurely optimising the design. – tymtam Oct 23 '16 at 21:49
  • 1
    @Tymski: That's not an opinion, but the mainstream opinion in UX community and the opinion of many authors, including the father of interaction design. See for example About Face 3 by Alan Cooper, Tree controls, p. 457 “Unfortunately, hierarchical trees are one of the most inappropriately used controls in the toolbox. They can be highly problematic for users [...]” So no, avoiding using a structure which would be inappropriate anyway in OP's case, I'm not really prematurely optimizing design. – Arseni Mourzenko Oct 23 '16 at 22:14
  • Good point. However, Facebook, The Guardian and many more UX conscious sites allow answers to comments. They enforce limited depth, but it's still a tree. I often find the flat structure of comments on StackExchange, combined with hiding some comments, unhelpful and limiting. – tymtam Oct 23 '16 at 22:24
  • 1
    @Tymski: well, there was an interesting discussion during one of FogCreek's podcasts about that, when they specifically blamed the tree structure of forums, but I can't find the podcast. In general, when it comes to discussions (and not Q&A, which is inherently different), a tree can in every case be replaced by a list with the ability to quote previous messages in order to keep the track of who you're answering to. For instance, StackExchange's chat follows this pattern (and a chat is a discussion, unlike a Q&A page), making it possible to quote previous comments. – Arseni Mourzenko Oct 23 '16 at 22:57
2

What about storing your comments in a table like the one below.

| parent | top parent/aritcle | comment | who | when | etc |

Having top parent column should allow you to get all relevant posts in operation. Then you can organise them in a tree, if needed.

As @arseni-mourzenko said - you don't need to optimise it too early. Good enough is often good enough.

  • What, in your opinion, would be the benefit of your approach over hierarchyid? – Arseni Mourzenko Oct 24 '16 at 11:00
  • 1
    1. Not db specific 2. Enforceable referential integrity – tymtam Oct 24 '16 at 22:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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