Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question
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. –  Chris Buckett Sep 22 '10 at 10:47
    
@ChrisB: for Oracle there is plunit.com/index.htm –  Tony Andrews Sep 23 '10 at 15:08
9  
Business logic please, not application logic - the aim should be the problem domain, not the technology choice! –  Phil Lello Apr 27 '11 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 '11 at 12:05
    
I wrote up my thoughts on this on my blog recently –  Gaius Sep 18 '11 at 14:52
add comment

12 Answers

up vote 37 down vote accepted

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.
share|improve this answer
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 '10 at 17:18
5  
Re: 4. Great! Let's use that VB logic in my Solaris Java app... oh, hang on... –  Phil Lello Apr 27 '11 at 0:15
11  
Phil, Great! Let's use your Oracle logic in my SQL Server app... oh, hang on... –  Craig Apr 28 '11 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 '11 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). –  Nick Chammas Nov 4 '11 at 21:04
show 3 more comments

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.

share|improve this answer
4  
"not as straight forward as it is with application code" why not ? –  NimChimpsky Sep 22 '10 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 '10 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... –  Jaco Pretorius Sep 22 '10 at 19:50
4  
@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 '11 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 '12 at 16:56
show 2 more comments

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.

share|improve this answer
    
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 '10 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... –  Jaco Pretorius Sep 22 '10 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 '11 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. –  JUST MY correct OPINION Apr 27 '11 at 0:51
2  
Sounds like your dba built a fence around the minefield to keep you safe. Be thankfull! –  James Anderson May 16 '12 at 1:37
show 3 more comments

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.

share|improve this answer
    
This. You want your database to be resilient. So logic tied to your data should reside there. –  Arkh Apr 23 '13 at 13:35
add comment

Relocated answer to dba.se, as it is the opposite opinion to the dominant one here

share|improve this answer
1  
"Interesting to note, most answers talk in terms of 'application logic', not 'business logic', as if the software isn't there to provide a business function." Very valid. :-) –  Denis Jun 2 '11 at 6:58
add comment

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.

share|improve this answer
    
+1 for logic in the database devalues the database. –  Drew Hall May 2 '11 at 10:20
4  
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 '11 at 23:56
2  
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 '12 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. –  Christopher Mahan May 15 '12 at 21:02
    
@ChristopherMahan, there is a lot you can do in database design to prevent bad data. –  HLGEM Mar 6 '13 at 14:39
add comment

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.

share|improve this answer
    
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. –  James Anderson May 16 '12 at 1:46
add comment

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.

share|improve this answer
3  
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 '13 at 9:39
add comment

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

share|improve this answer
3  
It's very hard to build application agnostic data warehouses with application based logic –  Phil Lello Apr 27 '11 at 0:17
add comment

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.

share|improve this answer
add comment

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?

share|improve this answer
    
All database code should be in source control in any event. It is code like any other. –  HLGEM Mar 6 '13 at 14:38
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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