designing a badge system, where to fire business logic? In code or stored procedures? or both?
architecture
Solution
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.
Problem
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: - list badges that pertain to the current user action - check if the user has a badge already or not - insert badge for user Potential options - business logic in the web application that calls stored procedures etc. - stored procedures ONLY - sql server job that runs every x minutes - 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!!