How to build a DB based ruleset for web app achievement system?

Question

I'm planning an achievement system for an ASP.NET MVC site. I want the achievement "rules" be stored in a database - to be easier to add new achievements and a central place to manage existing rules. Users will have no access to change the rules.

When a user performs an action that could potentially earn an achievement, the db rules will be queried and if there are matches, give them the achievements (stored in a lookup table, (userId, achievementId, dateAwarded).

At the moment I'm planning to put the "triggers" on certain actions in the controller, but the code that does the work will be in the model.

Is there standard DB schema for an achievement system that accomplishes this? No need to reinvent the wheel if not necessary. If not, what sorts of issues do you think would pop up, what to look out for?

Answer 1

You may find this answer helpful.

Speaking from experience, building a database-based rules engine for reacting to user actions is a very time-consuming, error-prone, painful exercise.

Instead, you can write any number of individual classes, each one responsible for knowing how to award one particular achievement. Use a base class or interface to give them all a common contract:

public abstract class AchievementAwarder
{
    //override to provide specific badge logic
    public abstract void Award();
}

You can foreach over each AchievementAwarder and call Award() on a recurring schedule. For example, you could have a "100 visits" achievement:

public class 100VisitsAwarder : AchievementAwarder
{
    public override void Award()
    {
        //get a visit count from the db for all users
        //award achievements
    }
}

This solves two problems:

1. It's orders of magnitude simpler, but much more flexible, than a database-based rules engine. As you can see, the individual units are extremely small and easy to change without affecting the larger system.

2. It can run asynchronously, so for achievements that require some heavy lifting to determine if they should be awarded, the user's normal activities are not impacted by the achievements engine.