Badge achievement system like SO: Data being used + Criteria(instant awarding & cron jobs)

Question

I've been looking at an open source clone of SO,

http://github.com/cnprog/CNPROG

I don't know Python/Django but I can still read whats going on,

The developers, seem to only be awarding badges with cron jobs, The awards are given out by the methods, being the "Rules" of the Criteria that must be met to achieve the award.

in this file, which is the Criteria,

Yet In the answer to best-way-to-store-badge-criteria its saying not run a query like,

"select count(*) from posts where user = :user"// for every post.

Which is kind of like the CNPROG method, but to instead

have a simple rule that watches each post come by, and "count them", storing the rules state in the user profile.

So by the words "counting them", does that mean it would be better to record everything in one table like,

class UserStats

      int voteUpCount
      int voteDownCount
      int score  
      int commentCount
      int viewCount
      int offensiveFlagCount
      int imageCount
      int feedbackCount
      int commentEditCount
      int commentDeleteCount
      int questionCount
      int questionEditCount
      int questionDeleteCount

Then with this data make rules from it, with a simple if(commentCount > 10)... upon every comment that the users posts, then preform the SQL query

Q If someone could further explain the answer from the question best-way-to-store-badge-criteria, But give an example with a "rule", "criteria" and database design

These would be used with some "per user action" and cron jobs to give badges

---

Answer 1

Yes - your intuition is correct. This is essentially what the database people would call a materialized view. For example, to give an achievement for post count > 30, do something like this:

posts {
   id,
   user_id,
   content
}

users {
   user_id,
   post_count,
   has_thirty_posts
}

When a post is inserted into the posts table, add 1 to post_count in the users table. If post_count > 30, set has_thirty_posts = true.

There are other ways to store the schema. This is just a gross simplification, but it should give you the idea. This is also called Denormalization (i.e. storing redundant data).