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.

  • 13
    I'd also be interested if any proponents of putting app logic into database layer can provide methods of unit testing that app logic. Sep 22, 2010 at 10:47
  • @ChrisB: for Oracle there is plunit.com/index.htm Sep 23, 2010 at 15:08
  • 10
    Business logic please, not application logic - the aim should be the problem domain, not the technology choice!
    – Phil Lello
    Apr 27, 2011 at 0:08
  • 1
    @ChrisB: I bet they can - populate tables with data (you'd have to create mock classes in the app layer), then call the SP automatically with a script. The whole thing can be written as a script of SQL statements, cleaning and setting up as it goes. Here's a link for 3 unit test SQL tools: toadworld.com/BLOGS/tabid/67/EntryId/67/…
    – gbjbaanb
    Jul 12, 2011 at 12:05
  • I wrote up my thoughts on this on my blog recently
    – Gaius
    Sep 18, 2011 at 14:52

11 Answers 11


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.
  • 5
    Can we add also the ability to frigging source-control the objects as they're modified? Maybe it's just my personal gripe ... (yes, I'm obviously omitting the ways that it can be done...)
    – jcolebrand
    Sep 22, 2010 at 17:18
  • 6
    Re: 4. Great! Let's use that VB logic in my Solaris Java app... oh, hang on...
    – Phil Lello
    Apr 27, 2011 at 0:15
  • 11
    Phil, Great! Let's use your Oracle logic in my SQL Server app... oh, hang on...
    – Craig
    Apr 28, 2011 at 23:45
  • 9
    @Craig The reality is that organisations change database vendors a lot less frequently than they switch applications or languages. However, my point was more that it isn't an advantage of app vs db, not that db is superior here; there's pro's and con's of either approach
    – Phil Lello
    Apr 29, 2011 at 16:17
  • 1
    @jcolebrand - Well, if you're doing database development, then VS 2010 is the bomb. I'm transitioning our group from SSMS to VS for development work and looking at adopting this TDD thing that's all the rage with devs. It's a worthwhile investment of time for any shop with significant database development work (and a bent towards SQL Server, of course). Nov 4, 2011 at 21:04

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.

  • 4
    "not as straight forward as it is with application code" why not ? Sep 22, 2010 at 13:06
  • @Nim - unless I'm doing it wrong, it involves database projects rather than the simple "edit it and it's checked out" you get when source control is integrated into your IDE.
    – ChrisF
    Sep 22, 2010 at 13:17
  • 1
    I would guess cause you can make changes to your database without committing it to version control, but you can't really do the same with code... Sep 22, 2010 at 19:50
  • 5
    @Jaco No, but you can run/release code without putting it in the SCM. Sloppy release management is sloppy release management whatever technology you use.
    – Phil Lello
    Apr 27, 2011 at 0:03
  • 3
    If you do all changes with scripts, then you just save them in the source control directory and check in and out just like any other code, There is no erason why it is hard to source control datbase work.
    – HLGEM
    May 9, 2012 at 16:56

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.

  • Expanding team size is hard enough without including someone who won't cooperate (not sure why the person in charge allows this choice) or requires their own 'tutoring session' to get them up to speed on the requirements.
    – JeffO
    Sep 22, 2010 at 12:48
  • 1
    My guess is it's because they sit around most of the time doing nothing, so when you finally give them something to review they want to really milk the attention. Maybe it's just me... Sep 22, 2010 at 19:49
  • 5
    @Jeff O Why wasn't the DBA involved in the design? DBAs tend to know a lot more about database optimisation than other developers, and should be treated as part of the team.
    – Phil Lello
    Apr 27, 2011 at 0:11
  • 8
    @Phil Lello: Because most software developers are arrogant cusses who think that they can learn it all themselves. This is why so many databases are such utter piles of junk. Apr 27, 2011 at 0:51
  • 2
    Sounds like your dba built a fence around the minefield to keep you safe. Be thankfull! May 16, 2012 at 1:37

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.

  • This. You want your database to be resilient. So logic tied to your data should reside there.
    – Arkh
    Apr 23, 2013 at 13:35

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.

  • 4
    This is the best answer. If the business logic is in the application, then if there are multiple applications using different logic the database can end up in a really bad state.
    – Sam
    Mar 6, 2013 at 9:39

Longish. see Summary at bottom.


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 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.


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.


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.


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.


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.

  • 5
    I really don't follow the database degradation point; by putting the logic in the database, you only need to change the rules in one place (the database) not many applications written in many languages. However +1 for a well thought out response.
    – Phil Lello
    May 5, 2011 at 23:56
  • 3
    I have to point out that many of the developers who think all thelogic should go in the application include data intergrity in that. That's one reason why so many databases have poor data integrity. And performance is one of the three most critical factors for a database (along with data integrity and security), so of course we are concerned about that!
    – HLGEM
    May 9, 2012 at 17:05
  • 1
    The data is only as good as the application. Garbage in garbage out and all that. If you have, hum, less-than-precise people writing the apps, there's very little you can do as a DBA to rectify the garbage. May 15, 2012 at 21:02
  • 1
    @ChristopherMahan, there is a lot you can do in database design to prevent bad data.
    – HLGEM
    Mar 6, 2013 at 14:39

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.

  • Well said. My big bug bear with stored procedures, referential integrity etc is error handling. All to often the end user is presented with "mulching error object YYYY procedure XXXXXX -- sqlcode -666" which results in time wasting support calls. When a simple "customer has no tax id" would let the user fix the problem and get on with his job. May 16, 2012 at 1:46

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

  • 3
    It's very hard to build application agnostic data warehouses with application based logic
    – Phil Lello
    Apr 27, 2011 at 0:17

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.


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?

  • All database code should be in source control in any event. It is code like any other.
    – HLGEM
    Mar 6, 2013 at 14:38

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.