views:

948

answers:

12

As a software engineer, I have a strong bias towards writing business logic in the application layer, while typically relying on the database for little more than CRUD (Create Retrieve Update and Delete) operations. On the other hand, I have run across applications (typically older ones) where a large amount of the business logic was written in stored procedures, so there are people out there that prefer to write business logic in the database layer.

For the people that have and/or enjoy written/writing business logic in a stored procedure, what were/are your reasons for using this method?

+1  A: 

Sometimes business logic is too slow to run on the app layer. This is especially true on on older systems where client power and bandwidth was more limited.

Byron Whitlock
+4  A: 

On a couple of ocassions I have put 'logic' in sprocs because the CRUD might be happening in more than one place. By 'logic' I would have to say it is not really business logic but more 'integrity logic'. It might be the same - some cleanup might be necessary if something gets deleted or updated in a certain way, and if that delete or update could happen from more than one tool with different code-bases it made sense to put it in the proc they all used.

In addition, sometimes the 'business logic line' is pretty blurry. Take reports for example - they may rely on stored procedures or views that encapsulate 'smarts' about what the schema means to the business. How often have you seen CASE statements and the like that 'do things' based on column values or other critieria? Could be construed as business logic and yet it probably does belong in the DB where it can be optimized, etc.

n8wrl
Good point...I have actually done similar things in the past.
senfo
+1  A: 

Two good reasons for putting the business logic in the database are:

  • It secures your logic and data against additional applications that may access the database that don't implement similar logic.
  • Database designs usually outlive the application layer and it reduces the work necessary when you move to new technologies on the client side.
dpbradley
If you have some kind of data-integrity or other business logic in the database, do you completely leave it out of your "Business Logic" layer? Or do you duplicate it? (For example, checking that a value is valid before entering in the DB, even though the DB itself won't let you enter it?)
Edan Maor
A database is for data persistence. If you did as you recommend, you are building for failure.Additional applications that need the same data should be controlled in how they access it and not by the data level itself. Too not do so and say, "Oh, the database controls that" is to open yourself up to huge oversights.Furthermore, database designs outlive applications because they are too difficult to change. This is also why you want to put as little into them as possible. The more logic you bundle with your data, the more you are going to have to work around as things change.
Ty
@Edan - duplicating the logic can make sense in certain cases where it improves performance by saving network round trips to/from the back end. You still have the safety net at the back end
dpbradley
@Ty - We could probably both point to examples from experience that support our opinions, but once you get up to sufficiently complex applications with hundreds of tables and high transactions rates that are accessed by dozens of applications that need to share data, I'll take my logic on the back end.
dpbradley
@dpbradley That is a point you should edit into your post. Performance. Depending on the nature of the data and the way it is being accessed, it can sometimes be necessary to do more of the logic on the database server for performance concerns. However, a highly complex system being accessed by dozens of applications should be doing so consistently. It is very hard to ensure those concerns at a database level and I would be very wary of trying to without some very good reasons.
Ty
Both those statements are just completely wrong. You have obviously missed the point.
Jack
@Jack - care to explain? Please post your own answer to the question if you have a counterpoint.
dpbradley
+3  A: 

You often find business logic at the database layer because it can often be faster to make a change and deploy. I think often the best intentions are not to put the logic there but because of the ease of deployment it ends up there.

Gratzy
A: 

The main reason for using the database to do the work is that you have a single point of control. Often, app developers re-use or rewrite code fragments in different parts of the application. Even assuming that these all work exactly the same way (which is doubtful), when the business logic changes, the app needs to be reviewed, recoded, recompiled. Unless the parameters change, this would not be necessary where the business logic is stored only in the database.

Rap
+1  A: 

My preference is to keep any complicated business logic out of the database, simply for maintenance purposes. If I get a call at 2 o'clock in the morning I would rather debug my application code than try to step through database scripts.

Mr. Will
+1  A: 

The primary reason I would put BL in stored procs in the past is that transactions were easier in the database.

If deployments are difficult for your app and you don't have an app-server, changing the BL in stored procedures is the most effective way to deploy a change.

Austin Salonen
+7  A: 
Matt Wrock
T-SQL and other SQL variants can be hard to read, but I would consider them declarative instead of procedural--cursors being one exception.
Scott A. Lawrence
+3  A: 

We do a lot of processing in the DB tier, where appropriate. There's a lot of operations you wouldn't want to pull back large datasets to the app tier to do analysis on. It's also an easier deployment for us -- a single point vs. updating applications at all install points. But a lot depends on your application and what it does; there's no single good answer here.

Joe
+3  A: 

Limiting the business logic to the application layer is short-sighted at best. Experienced professional database designesr rarely allow it on their systems. Database need to have constraints and triggers and stored procs to help define how the data from any source will go into it.

If the database is to maintain its integrity and to 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 the application layer is a data nightmare waiting to happen. Databases do not get information just from one application. Business logic in the application is often unintentionally bypassed by imports (assume you got a new customer who wanted their old historical data imported to your system or a large number of target records, no one is going to enter a million possible targets through the interface, it will happen in an import.) It is also bypassed by changed made through the query window to fix one-time issues (things like increasing the price of all products by 10%). If you have application layer logic that should have been applied to the data change, it won't be. Now it's ok to put it in the application layer as well, no sense sending bad data to the database and wasting network bandwidth, but to fail to put it in the database will sooner or later cause data problems.

Another reason to keep all of this in the database has to to with the possibility of users committing fraud. If you put all your logic in the application layer, then you must grant the users access directly to the tables. If you encapsulate all your logic in stored procs, they can be limited to doing only what the stored procs allow and not anything else. I would not consider allowing any kind of access by users to a database that stores financial records or personal information (such as health records) as I would not allow anyone except a couple of dbas to directly access the production records in any way shape or form. More fraud is committed than many developers realize and almost none of them consider the possibility in their design.

If you need to import large amount of data, going through a data access layer could slow down the import to a crawl becasue it doesn't take advanatge of the set-based operations that databases are designed to handle.

HLGEM
Yes, data can come from more than one application, but this doesn't mean you have to put the logic in the database. What's wrong with having another layer above the database that all applications must pass through? This is the reason behind n-tier system architecture. If you wanted to import a large amount as data why assume that this will be done direct to the database? A bulk import application could be developed using a service layer, which would then funnel that data through the same business logic layer...
Andy McCluggage
...Having multiple layer means that you can avoid a single layer doing too much. Databases are for data persistence only.
Andy McCluggage
All I can say is you have never spent months fixing bad data from poor designs like that. I have. Databases are not for data persiostence only.
HLGEM
The is nothing inherently poor about a properly structured, multi tiered design. You have clearly, like most, dealt with some specifically poor designs. I’ve been there too, and it has never lead to me think it was a good idea to put all business logic in the database. In fact, the biggest, most unfathomable messes I have seen are where this HAS been done.
Andy McCluggage
+1 on the point about SPs allowing for better security.
Rob Garrison
A: 

Your usage of the term "business logic" is rather vague.

It can be interpreted to mean to include the enforcement of constraints on the data (aka 'business rules'). Enforcement of these unequivocally belongs in the dbms, period.

It can also be interpreted to mean to include things like "if a new customer arrives, then within a week we send him a welcome letter." Trying to push stuff like this in the data layer is probably a big mistake. In such cases, the driver for "create a new welcome letter" should probably be the application that also triggers the new customer row insertion. Imagine every new database row insertion triggering a new welcome letter, and then suddenly we take over another company and we must integrate that company's customers in our own database ... Ouch.

Erwin Smout
A: 

I think Specially for older applications which i working on (Banking) where the Bussiness logic is huge, it's almost next to impossible to perform all these business logic in application layer, and also It's a big performenance hit when we put these logic in Application layer where the number of fetch to the database is more, results in more resource utilization(more java objects if it's done in java layer) and network issues and forget abt performenance.

santosh