Business logic in database versus code?

Question

As a software engineer, I am very inclined to write business logic at the application level, while I usually rely on the database no more than CRUD (Create Retrieve Update and Delete). On the other hand, I came across applications (usually older), where most of the business logic was written in stored procedures, so there are people who prefer to write business logic at the database level.

For people who have and / or use written / written business logic in a stored procedure, what were your reasons for using this method?

Answer 1

I am trying to seriously limit my business logic in the database to only processes that have to perform many queries and updates in order to complete one application operation. Some may argue that even this should be in the application, but I like to support IO, if possible.

Databases are great for CRUD, but if they are bloated with logic:

• It gets confusing where the logic is,
• Typically, databases are silos and do not scale horizontally in much the same way as application servers.
• t_sql / plsql is hard to read and procedural in nature
• You are losing all the benefits of OOAD.

Answer 2

The restriction of business logic at the application level is at best shortsighted. Experienced professional database designers rarely allow them in their systems. A database should have restrictions and triggers and stored procedures to help determine how data from any source will enter it.

If the database must maintain its integrity and ensure that all sources of new data or data changes follow the rules, the database is the place to put the required logic. Putting it on the application layer is a data nightmare awaiting its appearance. Databases do not receive information from only one application. The business logic in the application is often inadvertently bypassed by import (it is assumed that you have a new client who wants the old historical data to be imported into your system or a large number of target records, no one is going to enter a million possible goals through the interface, this will happen during import .) It also bypasses the changes made in the request window in order to fix one-time problems (for example, increasing the price of all products by 10%). If you have application-level logic that should have been applied to data modification, this will not happen. Now it’s normal to put it in the application layer, and there’s no point in sending bad data to the database and wasting the network on network bandwidth, but in order not to get into the database, sooner or later there will be data problems.

Another reason to save all this in the database is related to the possibility of fraud by users. If you put all your logic in the application layer, you must give users direct access to the tables. If you encapsulate all your logic in stored procs, you can limit them only to what stored procs allow, and not something else. I would not allow any user to have access to a database in which financial records or personal information (for example, health records) are stored, since I do not allow anyone but a couple of dbas to directly access production records in any form or form More fraud is committed than many developers implement, and almost none of them are considering developing them.

If you need to import a large amount of data, going through the data access layer can slow down the import bypass, because it does not benefit from set-based operations that are designed to process databases.

Answer 3

As much as possible, keep your business logic in the environment of the most tested and debugged . There are several good reasons for storing business logic in a database in other people's answers, but they are almost always far outweighed by this.

Answer 4

Your use of the term business logic is rather vague.

It can be interpreted as meaning the inclusion of a compulsory restriction on data (the so-called "business rules"). The fulfillment of these requirements clearly applies to the dbms period.

It can also be interpreted as meaning the inclusion of things such as "if a new client arrives, and then in a week we will send him a welcome letter." Trying to push such things into the data layer is probably a big mistake. In such cases, the driver for “creating a new welcome letter” should probably be an application that also starts a new client line insert. Imagine that each new insertion of a database row launches a new welcome letter, and then suddenly we take on another company, and we must integrate the clients of this company into our own database ... Ouch.

Answer 5

We do a lot of processing in the DB core where necessary. There are many operations that you would not want to drop large datasets into the application layer to analyze. This is also an easier deployment for us - one point or application update at all points of installation. But a lot depends on your application and what it does; there is no good answer here.

Answer 6

In several cases, I put the “logic” in sprocs, because CRUD can happen in more than one place. Logically, I would say that this is not business logic, but integrity logic. It can be one and the same: some kind of cleaning may be required if something is deleted or updated in a certain way, and if this removal or update can occur from more than one tool with different code bases, it makes sense to insert it into proc, all used.

In addition, sometimes the "line of business logic" is rather blurred. Take reports, for example, they can rely on stored procedures or views that encapsulate smart information about what a schema means to a business. How often have you seen CASE statements and the like that “do things” based on column values ​​or other criteria? It can be interpreted as business logic, and, nevertheless, it probably belongs to the database, where it can be optimized, etc.

Answer 7

I would say that if “business logic” means application flow, user control, synchronized operations and, as a rule, “doing business”, then it should be at the application level. But if this means that, regardless of how you dig into the data, it always makes sense and is a reasonable, non-self-contained whole, then checks to ensure compliance with these rules are included in the database, absolutely, without questions. There are always many ways to push data into the database and manipulate it when it is. Not all of these methods have a “business logic" built into them. You will find the SQL session in the database through the DOS window when you call support at 3 a.m., very liberal in that it allows, for example! If the logic is not in the database, to make sure that all data changes make sense, you can bet that the data will be very distorted over time. And since the system is just as valuable as the data it stores, it greatly reduces the return on investment.

Answer 8

Often you find business logic at the database level, as it is often easier to make changes and deploy. I think that often the best intentions are not to embed logic, but because of the ease of deployment, it ends there.

Answer 9

Two good reasons to put business logic in a database:

• It provides your logic and data against additional applications that can access a database that does not implement similar logic.
• Database projects typically survive the application level, and this reduces the work required when migrating to new technologies on the client side.

Answer 10

I work for a financial type company, where certain rules are applied by states, and these rules and their calculations can be changed almost daily, if not necessarily weekly. In this case, it made sense to move parts of the logic associated with the calculations to the database; where the change can be tested and applied without the need to recompile and reprogram the application, which is impossible to do daily without breaking the business. The stored procedure is tested, approved, applied, and the end user is no wiser. With the switch to web applications, there is less dependence on moving logic to the database, but is still present. Even web applications (depending on the language) must be compiled and published on the site, which can lead to downtime.

Answer 11

Sometimes business logic runs too slowly at the application level. This is especially true in older systems, where client power and bandwidth were more limited.

Answer 12

The main reason for using the database to work is that you have one management point. Often, application developers reuse or rewrite pieces of code in different parts of the application. Even if we assume that they all work in exactly the same way (which is doubtful), when the business logic changes, the application needs to be reviewed, transcoded, recompiled. If the parameters do not change, this is not necessary if the business logic is stored only in the database.

Answer 13

My preference is to exclude any complex business logic from the database, just for maintenance purposes. If I call at 2 a.m., I would rather debug the application code than try to execute the database scripts.

Answer 14

The main reason I would put BL in stored procedures in the past is because transactions were easier in the database.

If deployment is difficult for your application and you do not have a server application, changing BL in stored procedures is the most efficient way to deploy the changes.

Answer 15

I think Especially for older applications that I work on (at the bank), where the logic of the Business is huge, it is almost impossible to execute all this business logic at the application level, and It’s a big success when we put this logic in the Application layer, where the number of extracts to the database is greater, leads to more use of resources (more java objects if it runs in the java layer), as well as network problems and forgets about performance.

Answer 16

I am going to create and maintain a fairly large financial system, and I do not find logic in the application level for actions that affect or limit tens of thousands of records.

In addition to performance issues, when errors occur, fixing stored procedures is much faster than debugging the application, fixing, recompiling, redeploying code with longer downtime