Categories
database design-patterns mysql php

Best way to code Achievements system

I’m thinking of the best way to design an achievements system for use on my site. The database structure can be found at Best way to tell 3 or more consecutive records missing and this thread is really an extension to get the ideas from developers.

The problem I have with lots of talk about badges/achievement systems on this website is just that — it’s all talk and no code. Where’s the actual code implemention examples?

I propose here a design that I hope people could contribute to and hopefully create a good design for coding extensible achievement systems. I’m not saying this is the best, far from it, but it’s a possible starting block.

Please feel free to contribute your ideas.


my system design idea

It seems the general consensus is to create an “event based system” — whenever a known event occurs like a post is created, deleted, etc it calls the event class like so..

$event->trigger('POST_CREATED', array('id' => 8));

The event class then finds out what badges are “listening” for this event, then it requires that file, and creates an instance of that class, like so:

require '/badges/' . $file;
$badge = new $class;

It then calls the default event passing the data received when trigger was called;

$badge->default_event($data);

the badges

This is then where the real magic happens. each badge has its own query/logic to determine if a badge should be awarded. Each badge is set out in e.g. this format:

class Badge_Name extends Badge
{
const _BADGE_500 = 'POST_500';
const _BADGE_300 = 'POST_300';
const _BADGE_100 = 'POST_100';
function get_user_post_count()
{
$escaped_user_id = mysql_real_escape_string($this->user_id);
$r = mysql_query("SELECT COUNT(*) FROM posts
WHERE userid='$escaped_user_id'");
if ($row = mysql_fetch_row($r))
{
return $row[0];
}
return 0;
}
function default_event($data)
{
$post_count = $this->get_user_post_count();
$this->try_award($post_count);
}
function try_award($post_count)
{
if ($post_count > 500)
{
$this->award(self::_BADGE_500);
}
else if ($post_count > 300)
{
$this->award(self::_BADGE_300);
}
else if ($post_count > 100)
{
$this->award(self::_BADGE_100);
}
}
}

award function comes from an extended class Badge which basically checks to see if the user has already be awarded that badge, if not, will update the badge db table. The badge class also takes care of retrieving all badges for a user and returning it in an array, etc (so badges can be e.g. displayed on the user profile)

what about when the system is very first implemented on an already live site?

There is also a “cron” job query that can be added to each badge. The reason for this is because when the badge system is very first implemented and initilaised, the badges that should have already been earned have not yet be awarded because this is an event based system. So a CRON job is run on demand for each badge to award anything that needs to be. For example the CRON job for the above would look like:

class Badge_Name_Cron extends Badge_Name
{
function cron_job()
{
$r = mysql_query('SELECT COUNT(*) as post_count, user_id FROM posts');
while ($obj = mysql_fetch_object($r))
{
$this->user_id = $obj->user_id; //make sure we're operating on the right user
$this->try_award($obj->post_count);
}
}
}

As the above cron class extends the main badge class, it can re-use the logic function try_award

The reason why I create a specialised query for this is although we could “simulate” previous events, i.e. go through every user post and trigger the event class like $event->trigger() it would be very slow, especially for many badges. So we instead create an optimized query.

what user gets the award? all about awarding other users based on event

The Badge class award function acts on user_id — they will always be given the award. By default the badge is awarded to the person who CAUSED the event to happen i.e. the session user id (this is true for the default_event function, although the CRON job obviously loops through all users and awards seperate users)

So let’s take an example, on a coding challenge website users submit their coding entry. The admin then judges the entries and when complete, posts the results to the challenge page for all to see. When this happens, a POSTED_RESULTS event is called.

If you want to award badges for users for all the entries posted, lets say, if they were ranked within the top 5, you should use the cron job (although bare in mind this will update for all users, not just for that challenge the results were posted for)

If you want to target a more specific area to update with the cron job, let’s see if there is a way to add filtering parameters into the cron job object, and get the cron_job function to use them. For example:

class Badge_Top5 extends Badge
{
const _BADGE_NAME = 'top5';
function try_award($position)
{
if ($position <= 5)
{
$this->award(self::_BADGE_NAME);
}
}
}
class Badge_Top5_Cron extends Badge_Top5
{
function cron_job($challenge_id = 0)
{
$where="";
if ($challenge_id)
{
$escaped_challenge_id = mysql_real_escape_string($challenge_id);
$where = "WHERE challenge_id = '$escaped_challenge_id'";
}
$r = mysql_query("SELECT position, user_id
FROM challenge_entries
$where");
while ($obj = mysql_fetch_object($r))
{
$this->user_id = $obj->user_id; //award the correct user!
$this->try_award($obj->position);
}
}

The cron function will still work even if the parameter is not supplied.