Best approach for designing framework for badge assigning system

Question

I am currently designing one education site.

For this we need to assign some badges to user based on their activity (like stackoverflow).

I was looking for a framework for this. Any one have any idea, how this can be done? Table design etc.

Issues which I could think of with this are :

1. We may have large number of badges, so on each action of user we can not check all badges and check if any badge can be assigned
2. On adding a new badge and criteria for this, I dont want to change the existing code to handle this (can we have some generic solution?)
3. Which db should I choose (lets say if my framework in like, it raises the event when criteria for one badge satisfies and these rules are created in DB(like sql). or can I go for nosql db (like mongodb as my rest of the project is in mongodb )

My initial search gave me following things

1. rule engine -> will result in very tedious task Database Architecture for "Badge" System & Arbitrary Criteria (MySQL/PHP)
2. One project -> http://drupal.org/project/user_badges ( not able to understand it properly)
3. people are saying your table structure should be like this and this ..and all .. Best way to store Badge criteria? Creating "Badges" for my site, best way to store user badges? Using PHP

Answer 1

I would definitely choose a relational database over a nosql database for this approach.

The first thing you need is the underlying structure of a badge.

ID (int)
Title (Varchar 255)
Image (Varchar 255) - would hold location to image
Points (int)

You would then need to store all the badges a user has earned. So in your users section:

Badges (Varchar) - holds a serialized array of badge IDs 
Badge_count (int) - So you can get the total without parsing through Badges.
Point_count (int)

Now you say you can't query against all badges for each performed action, so you should also store an entry for each action. So say in your "posts" table for example:

Possible_badges (Varchar 255) - serialized array of Badge ID's

You can then normalize and give only the badges related to any specific task to your rule engine.

Your best bet is just to start playing with them, if you don't understand what is out there than you must compromise and go for basic logic so only award badges for tasks that can be measured by database entries (mostly numerical data). Or spend more time learning (and pulling your hair out).