Nested Comments in MongoDB

Question

I'm quite new to MongoDB and trying to build a nested comment system with it. On the net you're finding various document structures to achieve that, but I'm looking for some proposals that would enable me easily to do the following things with the comments

• Mark comments as spam/approved and retrieve comments by this attributes
• Retrieve comments by user
• Retrieve comment count for an object/user

Besides of course displaying the comments as it is normally done. If you have any suggestions on how to handle these things with MongoDB - or - tell me to look for an alternative it'd be appreciated much!

Answer 1

Have you considered storing the comments in all documents that need a reference to them? If you have a document for the user, store all of that user's comments in it. If you have a separate document for objects, store all comments there also. It feels sort of wrong after coming from a relational world where you try to have exactly one copy of a given piece of data, and then reference it by ID, but even with relational databases you have to start duplicating data if you want queries to run quickly.

With this design, each document that you load would be "complete". It would have all the data you need, and indexes on that collection would keep reads fast. The price would be slightly slower writes, and more of a headache when you need to update the comment text, since you need to update more than one document.

Answer 2

Because of you need retrieve comments by some attributes, by user, etc.., you can't embed(embedding is always faster for document databases) comment in each object that users can comment. So you need create separate collection for the comments. I suggest following structure:

comment
{
  _id : ObjectId,
  status: int (spam =1, approved =2),
  userId: ObjectId,
  commentedObjectId: ObjectId,
  commentedObjectType: int(for example question =1, answer =2, user =3),
  commentText
}

With above structure you can easy do things thats you want:

//Mark comments as spam/approved and retrieve comments by this attributes
//mark specific comment as spam
db.comments.update( { _id: someCommentId }, { status: 1 }, true); 
db.comments.find({status : 1});// get all comments marked as spam

//Retrieve comments by user
db.comments.find({'_userId' : someUserId});

//Retrieve comment count for an object/user
db.comments.find({'commentedObjectId' : someId,'commentedObjectType' : 1 })
           .count();

Also i suppose for comments counting will be better to create extra field in each object and inc it on comment add/delete.