What are the arguments against or for putting application logic in the database layer?

Question

Most software developers want to keep application logic in the application layer, and it probably feels natural for us to keep it here. Database developers seem to want to put application logic in the database layer, as triggers and stored procedures.

Personally I would prefer to keep as much as possible in the application layer to make it easier to debug and keep the responsibilities of the layers separate.

What are your thoughts on this, and what should or should not be ok to implement in the database layer?

Edit This question is also covered on dba.se, from the DBAs perspective. As programmers.se & dba.se have different audiences and biases, future readers might want to review both sets of answers before deciding what works best for them.

Answer 1

Off the top of my head, advantages of putting logic in the application layer.

1. Testability. This should be a good enough reason on it's own actually.
2. Better code structure. It's very difficult to follow proper OO-architecture with SQL. This usually also makes the code easier to maintain.
3. Easier to code. Due to all the different language features available in whatever language you're using it's usually easier to code in the application layer.
4. Code re-use. It's a whole lot easier to share code with libraries than sharing code in the database.

Answer 2

While it is possible to use version control with stored procedures (for example the Redgate database tools integrate with TFS), it's not always as straight forward as it is with application code.

My default position is that logic should be kept out of the database layer, however, there are times when it would be more efficient to implement the logic in the database. If that's the case then you must make sure you can track changes to this code.

Answer 3

In one company where I worked there was a lot of red tape involved with releasing code to production and involving a DBA for a code release was always a nightmare. We always put the logic in the application layer to eliminate having to deal with DBA's that were hard to work with. It's a totally lame reason but one derived from out of necessity.

Answer 4

Putting application logic in the DB sounds like a bad idea to me. OTOH putting logic in the DB that is specifically part of maintaining DB state (e.g. triggers/sprocs to update de-normalized tables) is a much different proposition.

---

Alternately, this can be stated as: there are good arguments for putting the logic needed to abstract away how the database really works from how you want it to look into the database.

Answer 5

Having read both questions, I think we may have all missed one critical point. The correct answer may depend on the type of software you are developing. The DBA group tends in large part to work on business critical Enterprise software systems and their answers tend to reflect what is necessary in that world. There is a huge difference in what is needed for those types of applications than what is needed for the next "Facebook" application. It's no big deal if you lose a couple of wall posts, it is if you lose a couple of orders or other financial transactions.

The people who work in the COTS (commercial off-the Shelf) world tend to need to be database agnostic for sales reasons and they want everything in complied code to make it more difficult to reverse engineer and replace their product with one that is homegrown. The Enterprise applications developed and maintained in-house will almost never need to change database backends except to upgrade.

Enterprise applications are also the ones which tend to have inputs from many places with the database being the only commonality. The system I work in has hundreds of different applications accessing it as well as hundreds of imports of client data, exports of data to clients and to data warehouses and uses mulitple reporting systems. The code that works well when adding one record is fails when I have to import 20,000,000. We got forced to use the application layer once because that was where the logic was and had to stop the process 18 hours later unfinished. The logic that should apply to all data records in a table needs to be in the database when you can't have one data layer that everyone uses.

Conversely when only one application will consume the data and the data is not the lifeblood of your company or is emphemeral, the rules are different and putting all of the logic in the application makes more sense.

Answer 6

Longish. see Summary at bottom.

RDBMS

A RDBMS stands for relational database management system. It's a system to manage a relational data base. The data is stored there. The data. It doesn't say business logic.

Business Process

What does business logic mean, really? To me, it's description of business processes in logical terms.

Processes are those business activities that occur regularly, enough that they are no longer ad hoc. These are different for every business.

Let me put my business cap on and explain what business means here. To some, this may come as a surprise.

Business

Business is the sum of the activities performed to achieve the creation of value, and more specifically value that can be traded. This could mean making combine harvesters, tuna sandwiches, or providing banking services. In most of the countries of the world, even those in non-capitalistic systems, people like to get the most value for their money, and therefore there is competition between different providers of these valued goods and services. The competition generally hinges on price, quality, and availability.

Quick detour: You need 40 million rivets in 2 days, you're not going to order from some guy on the internet with a paypal account, no matter how much cheaper his price is than that of your normal vendor.

Process Knowledge

As you can imagine, the processes involved in making this "value" mostly live in the executive heads. Some of that is put on paper and used as company policies and procedures. Some of that lives in the heads of corporate counsel. A lot of that lives in the head of the people running the divisions, departments, teams, and those running the machines, the cash registers, the ovens, the trucks. A small subset of that ever makes it down on the business requirements for software, and an even smaller subset of that is accurate by the time it's implemented in computer systems.

In the end, the business logic that you see in code is not that which runs a business, it is that which runs the application for the business. The actual brains inside actual people hold the actual business processes, and they have no problem understanding that the process in their brain is more accurate than the process in the computer. As an aside, you could probably not run the business if all you had were the policies and procedures of most corporations. Very often these are grossly inaccurate, despite herculean efforts.

So in the end, it is the application logic that is coded into the software. And people want to put that into the database, because database management system vendors have made grandiose claims.

Application Logic

I say no. I say application logic stays inside the application. The data goes in the database, in a very normalized way, and then gets ETL'd to the datawarehouse for reporting and drilling and rolluping and pivoting and cubing.

Data

I also say that the data outlives the application, so the data normalization effort should not be application specific, and not even business-specific, but rather should be business-general. Do you store state codes? You should use INCITS 38:2009 (http://www.census.gov/geo/www/ansi/statetables.html) because that's portable across businesses. This also makes it easier for multiple applications to manipulate the data.

NoSQL?

If you treat the database as part of the code of the application, from the tables layout to the triggers, stored procedures, and data formats, you're essentially using the enterprise database as a glorified BerkleyDB, which is a glorified flat files structure, which is really just persisted lists. This is essentially what NoSQL is doing: going back to the roots, but doing it in a multi-process, persisted, failure-tolerant manner.

Actual Code

No, you need to treat the data base as a common repository of data for multiple applications, both current and future. Now we come to the crux of my argument. Business processes change with the vagaries of the market, politics, and fashion. Very often they change faster than what coders can manage with computer-science-grade languages (Java, C#, C++ etc) and end up being written in VBA in excel spreadsheets in the accounting or marketing department. (And only if it can't be expressed in fancy vlookups...)

Database Degradation

The data doesn't change much if it's well organized. The business logic changes very fast. By putting business logic in the database, you make the database less valuable, because it will become obsolete and inaccurate sooner.

Summary

Data must outlive the application because business processes live in the application and business processes change much more often. Including business logic in the database is bad for its longevity and overall value.

Caveat

I've done my share of dba-ing and I've read the answers at dba.se but in all honesty what they're talking about is data-integrity issues and performance issues. I completely agree that people who touch corporate data should know what they're doing, whether dba or programmer or SAS senior analyst with read/write access.

I also noted that they recommend coders know SQL. I agree. It's a computer programming language, so I don't see why computer programmers would not want to know it.

Later on, after thinking about it

I think the middle ground is to make an API, and have that API manage the data flow to and fro. If you can't allow apps to connect directly to tables, at least you can make the access mechanism be in modern languages.

Answer 7

At risk of sounding dramatic, I'm genuinely horrified by the idea of application logic in the database. A lot of answers here have focused on the software development advantages, so the sake of brevity, I'm going to focus on the advantages yielded by the division of responsibility.

Databases provide an efficient means of storing and accessing information, while minimizing redundant data and producing logical relationships in the data. While database logic might be capable of implementing production-level business logic, my personal opinion is that the database should be as application-agnostic as possible to ensure that the data can be effectively leveraged by multiple applications while playing to the respective strengths of the database engine versus the strengths of the application's language of implementation.

One user on the DBA stack exchange stated this...

I want all the logic that has to apply to all users and all applications in the database. That's the only sane place to put it.

The last Fortune 500 I worked at had applications written in at least 25 languages hitting their OLTP database. Some of those programs moved to production in the 1970s.

... Followed by his belief that this is indicative of a violation of the DRY principle.

Rather than being a repetition of business logic, I think this is more likely a perfect example of the flexibility provided by a distinct division of responsibilities between the business layer and data layer.

Their OLTB database has been providing data reliably and efficiently to 25+ applications for decades! That's amazing! (Way to go!)

I can only assume that the data is agnostic enough to provide content for a number of distinct applications. Something that would be largely impossible if those developers attempted to hack something together using database logic.

As other answers have indicated, there are many other reasons not to implement a program in the database. I'm sure it would work, but the most probable result would be decades of regret, rather than decades of stability.

Answer 8

Database agnostic applications require all logic out of databases. It's very hard to build and maintain code to many different databases providers.

Answer 9

A good development will strike a good balance between the need for database integrity and speed by putting some of the logic in the database and most of it in the application.

Will the same query be used over and over again across many applications, then maybe it belongs into a stored procedure.

Making sure that house keeping fields are set when a row is inserted and updated is a DBA responsibility. A trigger will be used.

On the other hand, if I have business logic, it needs to be in the application. It should when possible make calls to stored procedures which will return the desired, filtered record set with the exact amount of fields needed. Not more, not less.

It's a matter of communication between teams and a matter of recognizing the pros and cons for each possibilities.

My opinion is: Don't make the application logic too deep in DB.

Answer 10

Some trading systems provide a way to extend the existing functionality with scripts, basically put in the database. My experience with this is rather negative, at least in a multi-user setting.

You'd put logic in a database, because you'd want to be able to modify that logic easily.

• How would you do version control?
  • What is the code history? What were the changes? By whom?
  • How yould you handle changes on the same code fragments?
• How would you identify and guarantee a consistent code state?

You could track this in an additional file-based VCS, but then what's the benefit of the database?

Answer 11

Most applications need to have some way to provide integration. Ideally, you would have a full API, a web service or at least make some database objects containing business logic available. Everyone is not in a time/resource position to build an API, so you have to compromise.