Business Logic in Database versus Code?

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?

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.

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.

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.

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.

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.

I try to seriously limit my business logic in the DB to only procs that have to do alot of querying and updating to perform a single application operation. Some may argue that even that should be in the app, but I like to keep the IO down if I can.

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

<ol>
<li>It becomes confusing where the logic is,
<li>Typically databases are a silo and do not scale horizontally nearly as well as the app servers.
<li>t_sql/PLsql is hard to read and procedural in nature
<li>You forfeit all of the benefits of OOAD.
</ol>

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.

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.

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.