Designing a system of icons, where can I run business logic? In code or stored procedures? or both?

Question

If you created an icon system similar to how SO does this, would you put the logical / business layer into the database directly (via a stored procedure, scheduled SQL jobs) or put it on the server?

From what I can come up with, you should:

• show icons related to current user action
• check if the user already has an icon or not
• insert icon for user

Possible options

• business logic in a web application calling stored procedures, etc.
• stored procedures ONLY
• Sql server job running every x minutes
• Windows service that starts every x minutes

Will a combination of these requirements be required? I think that since some of the icons are based on milestones for the question asked, is batch work perhaps better?

Update

A system where you can change the icon system, then re-run the entire icon link for everyone, will be even better. for example, you change the logic for some icons, now you must reapply it to all questions / answers / votes, etc.

interesting problem to solve !!

Answer 1

I would recommend placing all the business logic in the business layer. I recommend this for several reasons:

• Keep your business logic in one language / place
• Scalability - You can share data, implement various caching mechanisms, etc.
• The difference between the problems is that let your database do what it does best ... store data, allowing your programming language to make decisions on that data.

Answer 2

I would put it at the business level, because this is the business logic that we are talking about. Of course, stored procedures can be used to distract relevant data, but I am not a fan of implementing decision logic only in the database. If nothing else, simply because it becomes harder and harder to keep track of what happens when you revise the code later.

Answer 3

Personally, I would leave the database for storing / retrieving data and had the logic in the "business layer".

After the success of StackOverflow, I am very interested in implementing an achievement system for one of my sites, so I thought about it myself.

I am currently trying to assess the value of using a simple (in terms of processing power) procedure that I could perform in response to specific user actions (voting, new answers, etc.) that could save most of the icons updated as you progress through the site.

This will be supported by a heavier routine that can recount every icon from scratch. This can be run periodically from a service (or at least a simulated service ) to ensure that nothing was missed - but also in response to a change in skipping rules.

I assume that most of the answer to this will depend on the data on which you base the awards on the badge. The StackOverflow icons are apparently based on data (answers, questions, voices, etc.) and events (changes, re-tagging, etc.). So - the icon algorithm should probably interrogate some kind of audit log or action log.

Answer 4

So, this is a classic SO discussion and debate among passionate programmers. I asked a similar, but more general question about this ...

database-level business logic

To answer the first part, I found one of the best explanations I've seen about business logic in a code database and a database:

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

The following explains why business logic is much better and more scalable. I am also in this question ... therefore, to answer your question, I will not conduct business logic in the database or stored procedures, since the main reason among many others is that SPs are not version controlled, but its pain for version control. Not to mention the fact that IDEs for SP are infinitely more primitive than IDEs for code. Both sql / tsql and similar things are not intended for a complex code structure, but for basic data manipulation and, as you will see in this article, for some very simple code structure, because previously the client-server architecture was limited.

Some exceptions to this page:

There are two levels in the client server system that force you to implement at least two levels. Previously, the server was simply considered as a remote database, and the partition was considered as an application (client) and storage (server). As a rule, all business logic remained in the client, mixed with other layers, such as the user interface.

It did not take long to realize that network bandwidth could be reduced and centralized by logic to reduce the constant needs of client relocation by moving most of the business logic from the client. Since there were only two levels, the only place to move the business logic was the server. A server in architecture was a good place in a client server system, but a database as a platform was a poor choice. Databases were designed for storage and retrieval, and their extensibility was designed for such needs. Database stored procedure languages ​​were developed for basic data conversion in addition to what could not be done in SQL. Stored procedure languages ​​were designed to be executed very quickly, and not for complex business logic.

But this was the best of two options, so the business logic was moved to stored procedures. In fact, I would say that business logic was charred (hacked, adapted) to stored procedures as pragmatism. In a two-tier world, this was not perfect, but improved.

The business layer must contain all business rules.

This design has the following advantages: - All business logic exists in one place and can be easily checked, debugged and changed. - A true development language can be used to implement business rules. Such a language is more flexible and more suitable for such business rules, rather than SQL and stored procedures. - The database becomes a storage tier and can focus on efficiently retrieving and storing data without any restrictions related to the implementation of business logic or presentation tier.

So now, in terms of architecture, I would have each user icon updated by calling a stored procedure when the related question / answer or something else is updated. You put this in the business logic of a question or answer, as I assume that it will be different for different types of elements (when they change). Since this is an event-based action, the action will only occur when the event occurs. If you have a service or scheduled tasks, it will work all the time and, although it will be minimal, it will ultimately lead to a system crash when you have many users.

If you do not want each user’s events to run checks and updates in the gazillion, you can upload them to the table and complete the scheduled task for updating the icons.

So that the system can update the entire user base based on the new business logic, you can cover all your actions in a Windows task or a one-time task, and this will work better than tsql, IMHO and will be much more important and flexible.

However, sometimes duplication of VERY small business logic may come in handy for some performance improvement. But, as you see in the article, the business layer in the code is much more scalable, so it can be controversial.

Hope this is useful information for you to decide what you need ...

Answer 5

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

A typical rule can be implemented with a single INSERT statement in the following 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'm making it a little easier!)

Answer 6

I would highly recommend leaving the solutions to business logic rather than stored procedures. Stored procedures are designed to process data (i.e., collect data, verify specific conditions and conditions, delete, update, aggregate, etc.). This is not creating a place for conditional logic (decision making).

As for events, these verses are: everything in the dignity system (or, at least, maybe) is based on events.

data (answers, questions, voices, etc.) and events (changes, re-tagging, etc.)

... These are all events: answers to a question, asking a question, voting, etc.

You can use stored procedures to retrieve the data needed to determine if the badge was received, but your code must really make a decision and, if necessary, assign the badge. The algorithms for this would be as diverse as the icons. However, here is a little logic that I will adhere to:

• Categorize all the icons based on the types of events that they include (for example, answering a question, asking a question, editing, checking, voting, etc.).
• When a specific event occurs, take all the icons associated with this event (i.e. which can be obtained by completing the task that triggered the event)
• Go through each icon in this category and run it. Badge.VerifyCriteria (UserID) Method
• If the user does not already have an icon, assign an icon

The VerifyCriteria method will be a good example of a possible place for a stored procedure, especially if you need better performance. It also checks the database for a specific state or state, since it is a business logic. Some icons may require some processing, which is complex in a database language (such as SQL), so VerifyCriteria should be the actual method for an object that calls the appropriate stored procedures and / or code. It also helps your OO-friendly business logic.

If you want to completely rename everyone in the system, I would either run batch jobs in the background, or if the processing was easy enough, just update the user the next time you log in or next time Access to user data (for example, when someone tries view their profile). But I will stay in business logic anyway.

Answer 7

I would concentrate all business logic in a single language, logically separated in namespaces or packages. For example, all the work that needs to be done using the web interface will be displayed by the server using services.