Business Rules in the Database - what are the advantages and disadvantages?

Question

Maybe I join a team to develop a new project. The leader has already made some decisions. Among them, that "business rules will be in the database". In "procedures" and "views", "triggers", etc..

Upon learning of this, my first reaction was negative - I’m used to business rules in the application layer, and I don’t think it’s a good idea that they stay in the database.

However, the fact is that I really don’t know. I don’t have experience with this architecture either. So I ask: What are the advantages? What are the disadvantages? Is it good practice? It’s a bad idea?

Answer 1

I will try to focus this answer on relational databases, which I have the most experience and which should better cover the scope of the question.

Perks

• Data security and consistency are greater;
• The permissioning is done at the level of data, enabling other systems to directly access the base, which is quite positive in terms of performance;
• Since tables are not directly accessed in terms of writing (only in terms of reading, when this happens), data sanitization can be more efficient;
• Defaults, Checks and Rules avoid absence of data, defining standard data and avoiding insertion or updating of information outside the scope of the business;
• Correctly normalized, it is much faster in terms of reading than any ORM of any programming language, as there is neither the construction of the query nor the dynamic transliteration of the results.

Disadvantages

• Changes are costly: when you need to modify the schema of some table or key, all impacted objects need to be changed manually;
• Demands extra care as to tuning and performance as a result triggers affect the performance of readings and writings;
• It can cause deadlocks and other database-specific problems such as abnormal information generation by triggers;
• Being over-programmed, the behavior of the system can become obscure, as some programmers and analysts use indiscriminately artifacts such as Procedures and Triggers to perform heavy programming. For example, I’ve seen a system that ran an entire payroll calculation by calling Procedures in jail. It worked well and fast, but the implementation of an algorithm of this complexity entirely in a database is worrying and quite difficult to check;
• It can become a bottleneck if the technology does not support load distribution. The caching is entire in the database server;
• It does not work well with a simultaneous number of very large connections (in the thousands or even millions, for example); There is blocking of I/O.

Is a bad practice?

No. The feature set that today has a database was invented at a time when systems were like shells: only access interfaces organized to data from a database. A good example are languages MUMPS, Cobol, Natural and Sqlwindows, in which basically almost all the rules of massive data manipulation were made directly in the database.

However, we need to know that this is an old and conservative approach, so it is not focused on productivity. Maintenance and debugging is much more complex than an application layer of a modern system. It can become bad practice to extrapolate the purpose of the database, which is to store and hold information responsibly.

Answer 2

Perks

• It’s all in one place. It’s easier to maintain.
• Can performance gains. Software is optimized for this.
• Serves many applications using various technologies and languages without problems. Even allows direct access.
• Facilitates application developments by not having to deal with this logic.
• Allows more effective and granular access control to the bank.

Database is great at storing and retrieving information in various ways. When you want to do much more than that, you are using the wrong tool. Database is a mechanism, and he’s great at it. He doesn’t work very well trying to provide abstractions. When the mechanism is most important, you do it on.

There is application for everything. When the database is more important than the application, it is possible to make the logic work better inside the DB. A Data Warehouse, for example, benefits from this. If the database defines the solution well and the applications running on it are just satellites. If, in a way, the database becomes the application, it makes sense to use more of its resources.

Business rules and data rules are different things. Data rules fit well inside the database, always.

I know of other items that seem like advantages, but that there is a solution as good or better putting logic in the application. Just need to understand how. I will not put as an advantage things that are only advantages if it is done the right way and when you do it the right way in the application layer, there is also advantageous. For example, if you know what you’re doing in DB or the application, the data can be consistent. If you do not know, you will have problems doing in any of them. That’s why using what you (or your team) know is more important than how to do it. The response of Gypsy Morrison Mendez shows this. The first advantage he cites is only real if there are no other problems, especially, but not only, those mentioned by him in the first and third items of the disadvantages.

Disadvantages

• Virtually requires a DBA to optimize the database.
• Developers will need to know the language of the database well. It is rare to find professionals with knowledge and experience beyond the trivial. It is easy to find those who think they have both.
• SQL, even amplified, is not very expressive. It is usually less productive to program in any available variation. The codes are ugly and disorganized.
• Programmers don’t understand how to make database interfaces for applications properly.
• The database receives an extra workload that can precipitate the need for scheduling. And it is more difficult to scale the DB than the application.
• You get stuck with the database provider or work hard to replicate the logic to other vendors, losing the first advantage item.
• Applications depend on the database to be able to do various maintenance.
• It is illusion to think that you can put all logic in DB, so in practice you end up with a hybrid solution.
• Versioning is much more complicated.
• Tests are more complicated.
• Debugging is more complicated.
• Documentation is not so simple.
• Keeping development timing between the team is tricky.
• There are no such good tools to handle DB code.
• Makes code reuse difficult. Creates excessive coupling.
• A change in a field can generate a huge amount of cascade changes in other parts of the bank.
• Programmers hate this (opinion, ok, but this should be considered).

There are several ways to put logic into the application. Using a ORM is just one of them. The use of ORM often ends up being justification to put logic in DB, after all it is common for Orms to be complex and inflexible.

An application can do most of the business logic and leave to the bank what it can do better than the application. Beware of premature optimization. In a database this can produce contrary results more easily. If you choose to put business rules only as optimization you may be creating another disadvantage.

If an application only uses the database as a mechanism for storing data and it is it that is important in the solution, it is clear that putting more than is needed in DB is bad.

My experience

Particularly I use logic in the application, it suits me well, it comes cheaper for me. I work with software that are products. So:

• I need to have more control over the application.
• I need to support various database providers.
• I know that my application will be the only one to access the DB. I provide an API on an application server for other applications to access DB indirectly.
• I need to get rid of all the disadvantages that this brings more than the advantages it provides.
• The entire application, including database modeling, is mine and not the client’s, only the data is his.
• There is an established culture. It’s important to make decisions. It’s not just the technical that counts. Better use what you have expertise than to venture into something great that you don’t master. Unless the advantage is brutal.

Why am I saying this? So you can understand my situation and judge if I’m being biased. The advantages described exist even and it is difficult who can list advantages genuinely different from those presented, they can be detailed or specified better, but it is difficult to find something different.

Your case

First I tell you to enjoy the experience. Try to keep an open mind and try to learn a way to develop that you are not used to. Trying to change the course of what has already been decided will probably only bring you problems and will waste an opportunity to develop better.

Try using some of the items you are reading in the answers here to try to assess whether everything is correct in the project. But note that each project is different. Not all advantages and disadvantages will be found in all projects.

It would be terrible if you were in charge of the project and were doing something you don’t know.

The biggest question I could ask is: why will the project be centered on DB? If the decision was made with appropriate criteria, if it was thought, if it was taken into account all the points that could bring advantages or disadvantages, it is probably on the right track.

Is the project just an internal system? What is the background team? How will the evolution of this solution?

Good practice is to do the right thing in every situation, you will find out if it is the case in this project over time.

Completion

Basically if you ask a programmer (really) he will say that it is better to put in the application. If ask for the DBA (even the one that also program), it will say that it is better to put in DB. Most of the time the decision is made based on previous experiences or preconceived ideas of what is ideal. You choose which problems you want to deal with. It may seem that this is bad, but not necessarily. Surprisingly people decide without even knowing why.

Common sense and experience always count for more than any generic recommendation. The problem is that everyone has their own common sense and experience is influenced by several factors.

I put in the Github for future reference.