MongoDB Schema Design - vote on posts

Question

Suppose I have a website, for example digg.com I have a bunch of articles and people can vote for articles that they like.

I want to be able to request that the articles with the most votes for a given time (last hour, last day, last week) be sorted by the number of votes.

As usual in MongoDB there are several ways to implement this, but I'm not sure which one is the right one.

• A note document containing an array of votes - the votes themselves are documents containing the username, username and voting date:

{ "_id": "ObjectId(xxxx)", "title": "Post Title", "postdate": "21/02/2012+1345", "summary": "Summary of Article", "Votes": [ { "userid":ObjectId(xxxx), "username": "Joe Smith", "votedate": "03/03/2012+1436" }, ] } 

• A separate collection of votes containing detailed information on a separate vote and a link to the message to which it was voted:

 { "_id": "ObjectId(xxxx)", "postId": ObjectId(xxxx), "userId": ObjectId(xxxx), "votedate": "03/03/2012+1436" } 

The first one is more Documentey, but I don’t know how to request an array of votes to get the documents with the most votes in the last 24 hours.

I am inclined to the second, since it would be easier to request a vote count, grouped by voice, I think, but I'm not sure how well it will work. So you can do this in relational databases, but it doesn't look very documented, but I'm not sure if this is a problem?

Or am I using a combination of the two? Also I would make this type of aggregated request in real time, on loading each page. Or am I just running a query once a minute and storing the results in a collection of query results?

How would you implement this scheme?

Answer 1

A general way of tracking vote counts in general will be to save the number of votes in the mail document and update it atomically when a new value is clicked on an array of votes.

Since this is one update, you are guaranteed that the counter will correspond to the number of elements in the array.

If the number of aggregates is fixed and the site is very busy, you can expand this paradigm and increase additional counters, for example, one month, day and hour, but this can get out of control very quickly. So instead, you can use the new Aggregation Framework (available in version 2.1.2 dev, will be released in version 2.2). than Map / Reduce, and this will allow you to make the calculations that you want very simply, especially if you take care of saving the voting dates as an ISODate () type.

A typical pipeline for aggregation request for top recipients this month might look something like this:

 today = new Date(); thisMonth = new Date(today.getFullYear(),today.getMonth()); thisMonthEnd = new Date(today.getFullYear(),today.getMonth()+1); db.posts.aggregate( [ {$match: { "Votes.votedate": {$gte:thisMonth, $lt:thisMonthEnd} } }, {$unwind: "$Votes" }, {$match: { "Votes.votedate": {$gte:thisMonth, $lt:thisMonthEnd} } }, {$group: { _id: "$title", votes: {$sum:1} } }, {$sort: {"votes": -1} }, {$limit: 10} ] ); 

This restricts the input to the pipeline to messages that have votes, comparing the voting dates with the month you are counting, unwinds the array to get one document per vote, and then makes the “group” equivalent to sum all votes for each heading (I assume that the name is unique). Then he sorts by the number of votes and limits the output to the top ten.

You also have the opportunity to summarize the votes for the day (for example) for this month to find out which days are the most active for voting:

 db.posts.aggregate( [ {$match: { "Votes.votedate": {$gte:thisMonth, $lt:thisMonthEnd} } }, {$unwind: "$Votes" }, {$match: { "Votes.votedate": {$gte:thisMonth, $lt:thisMonthEnd} } }, {$project: { "day" : { "$dayOfMonth" : "$Votes.votedate" } } }, {$group: { _id: "$day", votes: {$sum:1} } }, {$sort: {"votes": -1} }, {$limit: 10} ] ); 

Answer 2

The scheme you have chosen largely depends on your use case. If you expect a lot of votes / comments and want to process them regardless of the position to which they belong, you can save them in a separate collection with postID as "foriegn key". However, if you want to download all the voices when downloading a specific message, and the voices themselves do not matter without the message in which they are placed, then go to the attachment (in your case, the first) approach.