where to put the Business Logic, AppLayer o DataLayer?

Question

After reading this post (business logic database or application layer) I still don't have the sufficient reasons to fight the "business logic in database" topic.

In my current work, there is a lot of db transaction (actually) and all that crappy code is hard to maintain, a lot of duplication in the stored procedures, so if you want change a value in a table for a bit, you will need to find all of those procedures and change them to what you want. Same happens if you need to change a little bit a table design.

All the current developers know SQL very well, but they still being not experts in any DATABASE as a engine(8 devs).

Currently we are planning to migrate the entire core to a new version (including database design). And I need some examples of:

• Why Business Logic in Database is sometimes EVIL ?
• How much and when Business Logic in Database is a good practice ?
• Why Business Logic in Application Layer is better for an enterprise application. ?

App Language: Java
Database: Oracle11g
The application will have Services, served as HTTP pages and as WebServices.

Answer 1

Have you read these AskTom threads?

• Application design - logic in Database vs logic in Web (Application) server
• Business Logic - PL/SQL Vs Java - Reg
• J2EE and Oracle's stored procedures

They are good reading and full of great wisdom from Tom Kyte. The only trouble is they don't support the answer you appeat to want - they support the opposite view!

Answer 2

Business logic in a data layer is usually perceived as evil for several reasons I'll try to stick to the general ones.

1. You can't unit test cleanly:

   One of the general ideas of unit testing is not only to tell you there is a problem, but to also tell where the problem is. If your BL is in the data layer and your BL tests are not working you can't tell if the problem is in your logic or in your data. Which leads to longer debug times.

2. Stubbing and mocking the entire layer:

   One of the main benefits of having a layered structure would be stubbing the whole layer. So your logic can evolve separately (maybe developed by a separate team) while you use a stub DAO layer so you're not worried about how and where from you get your data. This gives you the freedom to develop your logic without worrying that your domain model is even finished yet.

3. Tiers:

   If your layers are cleanly separated its much (much!) easier to have them working on separate physical instances (on different servers for instance). So for instance you can have several servers that just scale your data layer (which is not uncommon AFAIK). Obviously if your logic is there that would be much much harder (if at all possible).

Answer 3

Crappy code is indeed hard to maintain. That is the nature of crappy code - not where it is located. Moving to a "crappy code in the front end" solution is, well, not really a solution.

And if you think database structural changes won't affect business logic coded in the front end... ummm - logic dictates differently.

I think some things are well handled in the front end, and some things are better handled in the back end. And I think that a proper PL/SQL API design that operates at a business object level can mitigate structural change problems by isolating them from the other layers.

But if there is any thought to supporting other DBs, then this is also a problematic idea as not every DB supports the same constructs.

As to where business logic belongs - that may depend entirely on your application and, indeed, for performance reasons you may find it advantageous to have some aspects of it in multiple layers. That, of course, can cause maintenence issues but this all becomes part of the trade-offs neccessary to deliver a project or product.

But there sure ain't one strict universal answer.

Answer 4

- Why Business Logic in Database is sometimes EVIL ?

1. How to implement logging if you are using DB? Dont tell me that you are logging it to DB. :)
2. Hard to scale up.
3. Usually the database script is more difficult to read.
4. It's harder to test and mock.
5. What if you have to change Database? For example, your Oracle license is become not affordable by your company and you should change it to different Database. Migration will be a big problem.

- How much and when Business Logic in Database is a good practice ?

As minimum as possible. I usually only used it only if implementation in application takes a serious database performance hit. For this kind of reason, do it in PL/SQL is a better choice.

-Why Business Logic in Application Layer is better for an enterprise application. ?

The answers are the same as first answers.

Answer 5

It might be a poor design choice (not EVIL) if

1. You need to support multiple database vendors
2. You need to support different database versions
3. You need to support different database editions
4. You can DEMONSTRATE that adding logic to a non-database tier will lead to a reduction in CPU load on the database server, and that there would be a related licence cost saving. If adding that logic to the non-database tier results in more database requests, more network traffic, more database sessions in use, duplication of data caching etc then you can find that you are actually adding load on to the database layer and not saving anything.
5. Misuse of existing skillset. There would be little point in developing a new application in PL/SQL (or Ruby or .Net) if your entire staff is skilled in Java. Equally, if your staff has PL/SQL skills, it will be expensive to rebuild in Java.
6. Lack of, or expense, in tooling. While there are testing frameworks for Oracle, the commercial ones (eg from Quest) are better than the open-source ones.

Answer 6

For one thing, if you want to be able to migrate to a different database brand, you should not have your business logic in stored procedures.

Also, for complex domains, modeling the domain is much more natural on the Java side with an OO model, than on the DB side. OO lends itself well to expressing abstractions and relationships between them.

The canonical book on the subject is Domain Driven Design.

A reason to stay on the DB side may be performance. If you have huge amounts of business data, it may not be efficient enough to retrieve and manipulate it in the application. This is especially true to batch processing.

Answer 7

The problem with business logic in database is

• Expensive to scale. You're using oracle, so you know how much money costs to add another 16 core box that runs oracle, probably around u$s750k.
• You're locked to the vendor of the DB (technology wise too, you won't be able to migrate your code).

The benefit is

• One stop shop: All the DB clients, will run the same logic. If you're going to have several interfaces, then all can use the same logic.

I've worked for an insurance company that had all the logic in the DB, and it was more than fine, but VERY expensive. I think that any answer for your question will be very abstract, as there are many points to take into account to make a big architectural decision like this.

Answer 8

Why Business Logic in Database is sometimes EVIL ? You have to work through each update of each vendors, trouble if you want to move support from one vendor to other. Expensive as mentioned in one of post.

How much and when Business Logic in Database is a good practice ? Well logic for updating or deleting foreign key or related rows through a stored proc which does not involve in business logic should be fine.

Why Business Logic in Application Layer is better for an enterprise application. ? Well to start with easy to maintain, Application layer can exploit the frameworks that saves you a huge amount of time effort and money, to not to re-invent the wheel. Exploit threading or language specific power used for application layer.