Pros and cons of where to place business logic: application level or DB

Question

I always come across again discussions about where to place business logic: inside the business layer in the application code or down to the DB in terms of stored procedures . Personally, I would be inclined to the first approach, but first I would like to hear some opinions on your part, without affecting you with my personal views. I know that there is no one-time solution, and it often depends on many factors, but we can discuss it.

Btw, we are in the context of web applications (having Oracle DB), and our current approach is to

• A user interface layer that accepts user interface input and performs a first client-side check
• A business layer with a series of service classes that contains business logic, including validation for user input (server side)
• The level of data access that calls stored procedures from the database to perform persistence / read operations.

However, many people tend to move business-level material (especially regarding validation) to the database in terms of stored procedures.

What do you think about this? I would like to discuss.

Answer 1

I saw only one case where logic in Stored Procs made sense; this was mainly due to performance: a lot of data to move and crunch. The saving grace was that the logic was not extremely complex, but SP was still a nightmare. It was too slow to do this in application code.

So, guessing - maybe this is 1 out of 50+ senario projects?

You determine the factors that will be:

• Ownership: who (which system / component) owns the data and who owns the rules / logic that apply to it? If the database "belongs" to a specific component, then it should contain business logic, since the database is just a repository; if the database is an entity in its own right, then there may be a case of encapsulating logic. You may have an even more subtle gap when certain decisions are made in one place and others in another place. Triggers are a possible example.
• Managability: the logic in the application code is much better support for testing, etc.
• Difficulty: (related to manageability) logic in the application code.
• Performance: If the amount of data is large and the use of the application code is too slow, you may be forced to put it there.
• Consistency: everything is in the application code - and I hope that you have no performance problems. make sure you document exceptions well.

Answer 2

Logic in the database is a nightmare to service. In cases where it is really necessary, you need to document it well and put it in text format along with other source code.

Answer 3

Having the data goes without saying whether anyone using your application, another application or SQL tool can be valuable in itself.

Got a value that should never be NULL? - Well, create a database to enforce this rule. Got a relationship between two tables that should always exist? - ok, put a foreign key constraint.

Got values ​​that should be unique in your area of ​​concern? - Good, add a unique constraint. Got a string that should always be between 6-10 characters? - ok, add a control constraint.

All of them are basic, easy to add to the database and give you confidence that your application will not crash when it tries to load something from the database that someone was looking for manually. And to some extent they can be considered business logic. (In the end, you extract all of this from concrete facts about your problem area).

Thus, I would put such business logic into the database. Yes, in your application you would like to apply similar checks to provide a more pleasant user interface. But I would rather have my application crash (as a last resort) when it tries to put something invalid into the database than detect this fact after 6 months.

Answer 4

He usually sucks for various reasons. If you work with object-oriented, then stored procedures are not eaxactls a good place for logic, because your objects no longer exist there. An object can be in several tables.

Secondly. SQL is a damn bad langauge for coding complex logic. There is simply no tdone for this - one of the reasons SQL Server allows you to write SP in .NET. Try to compute the hash in SQL, and you will understand what I mean - all kinds of string manupulations - this is another area. Dirty as hell.

SPs are generally quite often made with idiotic arguments. Idiotic, as the arguments that people urge to protect, simply do not correspond to reality. Frans Bourma has a list of the most commonly used errors and a good explanation of why the arguments are mostly dumb strokes at http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx - and yes, this is it level of idiocy (for example, people don’t even read the documentation or think about what they actually say in all the consequences).

I personally have limited systems with stored procedures, and I have: Limited complexity, but high performance. In principle, there is no inheritance, because the object model is simple, the transaction logic in SP is not too complicated, and I need / need an extreme low lock speed, so certain operations are moved to stored procedures. In addition, this particular application also has a very unusual object model (aare objects are dynamically streams from different sources, never updated, always replaced, and all changes HAVE to go through services and not be performed on the object - sometimes due to a change, I asked for another computer in another country in the organization’s organization.

A good example is an accounting system with high performance (as it tracks the transactions of fully automated trading systems). The logic in each SP is not very complicated, but I want to have as few SQL as possible going back and forth.

Now, the bad side of stored procedures is also a lot of useful. There is no proper testing framework, no mocking frameowrk, itnegration source control is a little awkward (but doable with the right set of tools). Integrated Debugging? Well, my huge thanks to Microsoft and Visual Studio - it really works (the breakpoint in the stored procedure is very nice).

I have yet to see one approach using a variety of stored procedures that were not protected with completely conflicting arguments - at the level of actually demonstrating the “employee must be fired” thinking level. Maybe they are there, but I have not seen them.