designing a badge system, where to fire business logic? In code or stored procedures? or both?

Question

Hi,

If you were to build a badge system similiar to how SO does it, would you put the logic/business layer in the database directly (via stored procedure, scheduled sql jobs) or put it in the server side?

From what I can think of, you have to:

1. list badges that pertain to the current user action
2. check if the user has a badge already or not
3. insert badge for user

Potential options

1. business logic in the web application that calls stored procedures etc.
2. stored procedures ONLY
3. sql server job that runs every x minutes
4. windows service that runs every x minutes

Would a combination of these be required? I think it would since some badges are based on milestones for a given question, maybe a batch job is better?

Update

A system where you can modify the badge system, then re-run the entire badge linking for everyone would be even better. i.e. say you change the logic for some badges, now you have to re-apply it to all the questions/answers/votes/etc.

interesting problem to solve!!

Answer 1

I would write a stored procedure, since all the information needed resides in the database so this is the most efficient place to access that data.

A tyical rule could be implemented via a single INSERT statement along these lines:

IF eligible for <new badge> THEN
    INSERT INTO user_badges
    SELECT <new_badge>
    WHERE NOT EXISTS (SELECT NULL FROM user_badges
                      WHERE badge = <new_badge>);
END IF;

(I simplify somewhat!)

Answer 2

I would put it in the business layer, after all this is business logic that we are talking about. Stored procedures can of course be used to pull back the appropriate data, but I am not a fan of implementing decision logic solely in the database. If nothing else just because it becomes harder and harder to track what is going on when revisiting the code later on.

Answer 3

I would recommend putting all business logic in the business layer. I recommend this for a few reasons:

• Keep the business logic in one language / place
• Scalability - You can partition data, implement different caching mechanisms, etc.
• Seperation of concerns - let your DB do what it does best...store data, let your programming language make decisions on that data.

Answer 4

Personally, I'd leave the database to do the data storage / retrieval and have the logic in a 'business layer'.

Following the success of StackOverflow, I'm quite interested in implementing an achievements system for one of my sites, too - so I've been giving this some thought myself.

I'm currently trying to assess the value of having a lightweight (in terms of processing power) routine which I could run in response to specific user actions (up-votes, new answers, etc.) which could keep most of the badges up-to-date as the site goes.

This would be supported by a more heavyweight routine which could recalculate every badge from scratch. This could be run periodically from a service (or at least a simulated service) to make sure nothing had been missed - but also in response to a change in badge rules.

I guess a big part of the answer to this is going to hinge around the data you're basing the badge awards on. The StackOverflow badges appear to be based on both data (answers, questions, votes, etc.) and events (edits, re-tagging, etc.). So - the badge algorithm must presumably be interrogating some sort of audit log or 'actions' log.