Design help

itaym02

In the site stackoverflow.com there is a "badge" system. Users earn badges for various actions they do in the system. For instance: get 10 upvotes, or visit the site for 30 days...
I have an open source version of that system (see signature), I am trying to think the best way to implement such a system. A point to bear in ind is the system might run on servers without cron.
Do I check each request for the user if he has earned one of the 50 or so badges possible?
Do I check each badge in the right place (10 up votes, in the voting code, 30 days in the login code etc). In a case like that, I wouldn't be able to give the users of the system the ability to define new badges without going into the code...
Do I emulate a cron to check all users for all badges each X minutes?

What options do I have?

ixalmida

For that matter, you can just set up a client machine to access a hidden "check badges" script. The page can refresh via Javascript, thus running it again, or you can set up a scheduled task/cron on the client machine. The script doesn't necessarily need any validation or security because executing it won't hurt anything.

bpat1434

If you can't use a literal cron (or scheduled task) then you can "fake" one. You could simply create a simple table (or a text file) which holds a date. For each request, you can quickly look in that file and compare that time with the current. If a specific amount of time has passed, then run the custom script, otherwise, don't.

As far as adding badges dynamically, you might think about adding a join table to join badge_id to member_id and when they've completed the badge, add them to that table. Then there's no expensive query to check if they've completed something during every request. It would then be a simple query to see what badge_id's that user has.

itaym02

As far as adding badges dynamically, you might think about adding a join table to join badge_id to member_id and when they've completed the badge, add them to that table. Then there's no expensive query to check if they've completed something during every request. It would then be a simple query to see what badge_id's that user has.

I liked this idea, I will use that for the user-action badges. Thanks.
Now I have to think about event driven badges (for example, a question that gets voted uo-by various users - more than X times.

bpat1434

Those types of events would be simple. If you have a predefined list of events, then just make sure that you record the user id of the user that does each event. Then when you record another event of the same type, you just do a quick count and if they've completed said badge requirement, add it to the badges table.

For example, I may have an badge_events table which just lists the event title and an ID for that event. Then a badge_events_occurrences table which records the event id, user id, and some other pertinent information to the event.

It's an idea, and very rough around the edges; however, it's the first path I would head down.

itaym02

My worries are the performance issues for each calculation (questionsXusersXvotes)...

bpat1434

You would only be doing it a select few times, and then once they successfully complete that goal, you set a flag. No real performance issues to worry about that I can see. You don't need to calculate everyone's statistics, just the current user.

itaym02

Not exactly.
Say you get a badge for each question or answer you gave and got 10 up votes.
So, basicly I will have to traverse ALL the questions and ALL the answers every few X seconds.
There are ways to avoid this, but there similar cases, where you can't just monitor the user actions.

bpat1434

So what you're saying is that if I count the number of votes any question I ask or answer I give has, then divide it by 10, that lowest whole number is the number of badges I have.... e.g.

$query = "SELECT COUNT(*) AS `count`
FROM `vote_table` v
LEFT JOIN `questions` q
ON v.question_id = q.id
LEFT JOIN `answers` a
ON v.answer_id = a.id
WHERE q.user_id = {$user_id}
OR a.user_id = {$user_id}";
$result = mysq_query($query);
if(!$result)
{
    echo 'No badge yet';
}

$row = mysql_fetch_assoc($result);
$badges = floor($row['count'] / 10);
echo 'User has '.$badges.' badges.';