26

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!!

Chris Roberts
  • 18,622
  • 12
  • 60
  • 67
Blankman
  • 259,732
  • 324
  • 769
  • 1,199
  • Issued a bounty. I have the same question, but the accepted answer doesn't even answer the question. – Layke Aug 28 '11 at 10:29

7 Answers7

19

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.
Rich Kroll
  • 3,995
  • 3
  • 23
  • 28
8

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.

Jack Ryan
  • 8,396
  • 4
  • 37
  • 76
  • +1. Keep your business logic out of the database unless you absolutely need the performance increase on a particular query/SP. In real life you rarely do; this would be textbook premature optimisation. – bobince Nov 24 '08 at 16:10
  • 1
    As a comment, I would add, read Meta. Lots of information are given there about the actual code behind the badges, and their reasons. – Kheldar Sep 03 '11 at 16:04
5

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.

Community
  • 1
  • 1
Chris Roberts
  • 18,622
  • 12
  • 60
  • 67
3

So - this is a classic SO debate and a debate amongst passionate programmers. I've asked a similar but more generic question about it...

business logic in database layer

To answer the first part, I found one of the best explanations I have seen about business logic in code vs database is here:

http://www.codeproject.com/KB/architecture/DudeWheresMyBusinessLogic.aspx

It goes on to explain why business logic is much better and scaleable. I'm on that mindset as well... so to answer your question, I would keep no business logic in the DB or stored procs, for the main reason amongst many others being that SPs aren't version controlled, and its a pain to version control them. Not to mention, IDEs for SPs are infinitely more primitive than IDEs for code. And sql/tsql and things like that were not meant for complex code structure, but basic manipulation of data, and as you'll see in the article, for some very basic code stucture because previously client-server architecture was limited.

Some excepts from this page:

In a client server system there are two tiers thus forcing at least two layers to be implemented. In the early days the server was simply viewed as a remote database and the division was seen as application (client) and storage (server). Typically all the business logic remained in the client, intermixed with other layers such as the user interface.

It did not take very long to realize that the network bandwidth could be reduced and logic centralized to reduce constant redeployment needs of the client by moving much of the business logic out of the client. As there were only two layers, the only place to move the business logic to was the server. The server architecturally was a well suited place in a client server system, but the database as a platform was a poor choice. Databases were designed for storage and retrieval and their extensibility was designed around such needs. Database stored procedure languages were designed for basic data transformation to supplement what could not be done in SQL. Stored procedure languages were designed to execute very quickly and not for complex business logic.

But it was the better of the two choices so business logic was moved into stored procedures. In fact I would argue that business logic was shoe horned (smashed in, made to fit) into stored procedures as a matter of pragmatism. In a two tier world, it was not perfect but was an improvement.

The business layer should contain all of the business rules.

Such a design has the following advantages: - All the business logic exists in a single location and can be easily verified, debugged, and modified. - A true development language can be used to implement business rules. Such a language is both more flexible and more suited to such business rules rather than the SQL and stored procedures. - The database becomes a storage layer and can focus on efficiently retrieving and storing data without any constraints related to business logic implementations or the presentation layer.

So - now, in regards to the architecture, I would do it so that each users badges would get updated via calling a stored proc when the related question/answer or anything else gets updated. You put this in the business logic of the question or answer, as I assume that it will be different for different types of items (when they get modified). Because this is a event based action, the action would only happen when the event happens. If you have a service or scheduled tasks, it will run all the time, and though minimal, it will bog down the system eventually when you have a lot of users.

If you don't want to have each users' events to trigger a gazilion checks and updates, you can batch them into a table, and have a scheduled job to update the badges.

To allow for the system to update an entire userbase based on new business logic, you can encompass all your actions into a windows job, or a one time job, and that would function better than tsql, IMHO, and would be much more maintable, and flexible.

However, sometimes it may benefit you to duplicate VERY little of the business logic, for some performance gain. But as you see in the article, business layer in code is much more scaleable, so it may be a moot point.

Hopefully this is useful information for you, to decide on what you need...

Community
  • 1
  • 1
M.R.
  • 4,737
  • 3
  • 37
  • 81
  • thanks for the nice writeup :) it definitely makes sense to put all the business logic in one place. Also, if you are giving a dedicated machine to the database to manage all the data (eventually you go there), then why burden the machine cycles with this stored procedure code. – Lazylabs Sep 03 '11 at 17:13
2

I would strongly recommend leaving decisions to business logic, not to stored procedures. Stored procedures are for processing data (i.e. gathering data, checking for particular states and conditions, deleting, updating, aggregating, etc.). It's not a create place for conditional logic (decision making).

As for events, verses data: everything in a merit system is (or at least can be) event based.

data (answers, questions, votes, etc.) and events (edits, re-tagging, etc.)

...All of these are events: answering a question, asking a question, casting a vote, etc.

You can use stored procedures to get the data you need to determine if a badge has been earned, but your code should actually make the decision, and if appropriate, assign the badge. The algorithms for this would be as varied as the badges, perhaps. However, here is a little logic I would follow:

  • Categorize all badges based on the types of events they involve (e.g. answering a question, asking a question, making an edit, retagging, voting, etc.)
  • When a particular event occurs, grab all the badges associated with that event (i.e. that can be earned by completing the task that triggered the event)
  • Cycle through each badge in that category and run it's Badge.VerifyCriteria(UserID) method
  • If user doesn't already have the badge, make the badge assignment

The VerifyCriteria method would be a good example of a possible place for a stored procedure, especially if you need higher performance. This is as much checking the database for a particular state or condition, as it is business logic. Some badges may require some processing that is difficult in a database language (e.g. SQL), so VerifyCriteria should be an actual method on the object which calls stored procedures and/or code as appropriate. This also keeps your business logic OO-friendly.

If you want to completely re-badge everyone in the system, I would either run batch jobs in the background, or if the processing is light enough, just update the user the next time they log in, or the next time they're user data is accessed (e.g., when someone tries to view their profile). But I would keep it all in the business logic still.

Trevor
  • 13,085
  • 13
  • 76
  • 99
  • 1
    The problem I think is that maybe half of the "events" might be triggered by the user himself.. or at least someone else while they are logged in. So do you try and calculate all of this in real time. Potentially adding 20+ queries per event to that page. Seems like there would be a lot of logging required. – Layke Aug 29 '11 at 20:40
  • 1
    Good point. StackOverflow doesn't seem to update things immediately, and perhaps that is why. Perhaps you could modify the above algorithm to use a task queue _set_, which would delay processing for a certain period of time (maybe 5 minutes), allowing each user's task (e.g. the list of events that need to be checked) to be updated before actually doing the processing. This would also make the system more scale-able since it would spread out the load of peak hours while the queue is processed. – Trevor Aug 29 '11 at 22:21
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!)

Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • you will be creating too many variables in sql, not to mention tons of control of flow statments that will make it pretty messy don't you think? – Blankman Nov 24 '08 at 17:04
  • No, I don't think that. It will be a self-contained stored procedure with 1 parameter - user ID. Mind you, I work in Oracle which has a fantastic stored procedure language, PL/SQL. Maybe in other DBMSs things aren't so good? – Tony Andrews Nov 24 '08 at 17:38
  • But you have to track previously assigned badges, so and IF they are not assigned, assign it if the current action necessitates it. So you will have variables to store the current status of a badge. Messy I think! – Blankman Nov 24 '08 at 17:43
  • one advantage is that you can modify on the fly w/o recompiling the app. – Blankman Nov 24 '08 at 17:44
  • Your comment about "you will have variables", implying variables are a "bad thing". I don't understand why, but perhaps your DBMS handles variables strangely? Which DBMS do you have in mind? To me, variables are an internal part of the stored procedure and are not a problem. – Tony Andrews Nov 24 '08 at 17:51
  • Tony, sqlserver. Yeah right they are not a bad thing, I for some reason don't like making a sproc with 30-40+ variables, and hard coding threshhold values inside of a sproc. Do you agree at some level with any of my objections? :) – Blankman Nov 24 '08 at 18:51
  • Tony, say a particular action might make some eligible for 15 badges, and say the user has all of the badges already. Your sproc is going to make 15 queries for no reason! not good with load. – Blankman Nov 24 '08 at 18:56
  • I don't really agree with your objections, but my experience of SPs seems to be different from yours. As for hard-coding thresholds in a SP, the arguments seem no different from those about hard-coding anywhere. You could hold them in a table instead. – Tony Andrews Nov 25 '08 at 10:20
  • "Your sproc is going to make 15 queries for no reason!" - no, with the reason that we need to find out whether the user already has the badge. How else will you know if you don't look? Again, you seem to me to have unfounded fears... – Tony Andrews Nov 25 '08 at 10:21
  • Well if you do it in code, you can just pull a single query to load all the badges, then you don't need to check if the person has it for each individual badge in question. – Blankman Nov 25 '08 at 13:47
  • Blankman, all your comments seem to be based on the assumption that querying the database from an SP is a BAD thing to do. Now, SQL Server must be very different from Oracle for that to be the case. Look at it the other way, you propose to load in ALL my 57 badges whether relevant or not... – Tony Andrews Nov 25 '08 at 14:39
  • Hey don't knock sql server, its just my opinion hehe. Leave sql server out of it, its innocent I tell ya! When you can put somethign in the business logic layer (which you can cache also), why put business logic into the db layer? – Blankman Nov 25 '08 at 15:40
  • For me, business layer = Oracle PL/SQL packages (stored procedures). The application layer just collects user input, calls stored procedures, and displays results. – Tony Andrews Nov 25 '08 at 16:38
0

I would concentrate all business logic in a single language logically separated in namespaces or packages. All the work needed to be done by web interface for instance, would be exposed by the server using services.

Rod
  • 401
  • 2
  • 9