Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

NOTE The audience of programmers.se and dba.se is different, and will have different viewpoints, so in this instance I think it's valid to duplicate What are the arguments against or for putting application logic in the database layer? on programmers.se.

I couldn't find discussion on dba on this already, and the original post says it all, so:

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?

N.B. I'm not the OP for that question, but left the original wording intact.

share|improve this question
3  
I wrote up my answer to this in my blog. My conclusion is, doing it in the application just doesn't scale once you consider the entire application lifecycle. –  Gaius Sep 18 '11 at 14:49
3  
Comparing the answers here and on SO, the gap is striking. The developers protest at the delays entailed from centralizing processes in the database, but to the DBAs that's a good thing. Forcing people to put more time and effort into requesting a new view or sproc reduces the number of contact points with the data, making it easier to maintain consistency and reducing the number of points of optimization. –  Jon of All Trades Apr 10 '12 at 21:35
    
How does it slip past everyone that SQL Server is a software application? It isn't magic. SQL Server is a software program. Your business/application layer is a software program. Put things where they make sense. –  Andrew Finnell Jun 14 '12 at 0:41
add comment

9 Answers

One con that people aren't necessarily discussing - the pros have been exhausted here - is cost.

The CPUs on the database server are frequently the most expensive CPUs in any organization when baking the cost of software licensure. So moving business logic to the data tier is something that should be done judiciously, not necessarily uniformly.

share|improve this answer
    
You are the only person to mention the CPU. Your answer is vital here because certain application logic has no place in Stored Procedures, such as Trig Functions for Geospatial Logic, Standard Deviations, Amortizations, and things like these. They belong in app source code. +1 !!! –  RolandoMySQLDBA Mar 7 at 15:31
add comment

I wrote up my answer to this in my blog. My conclusion is, doing it in the application just doesn't scale once you consider the entire application lifecycle.


3. Add integrity/check constraints to the underlying database, with more complex code implemented in the database’s stored procedure language. From this we get one central location to maintain and we get absolute enforcement of the rules even for applications that we don’t know about! We get one language to express the business rules in, throughout the entire application portfolio and lifecycle, since the language du jour changes far more often than the database. And it runs on a system that is already as mission-critical as the most important applications. Errors are handled by the existing code that handles database errors in those applications. There is still the risk that an application might break of course, but of the three scenarios, this is the least, and only the broken application needs any modification, not all of them (and most SP/database mechanisms will allow an exception to be made for one application if that is really, really necessary). Think this doesn’t matter in your greenfield site or small company? Well if your business succeeds, in 30 years time you will wish you had paid heed to my wisdom!

…Some [objections] I often hear:

  • It’s difficult to version control SP code deployed in the DB. I don’t think that’s any truer than saying it’s difficult to version control Java code deployed in an app server, which is to say, it isn’t difficult at all, it’s commonplace. And over in Ruby-land, entire books are written just about how to get your code from a development environment into production, something that no other language community seems to struggle with. Yet version controlling a stored procedure is apparently too difficult.
  • Stored procedures are hard to test. This is a strange one. For a start, SPs are strongly typed; the compiler will tell you if there’s a code path in or out that doesn’t make sense, and in Oracle at least, will calculate all the dependencies for you. So that’s one set of common unit tests you might need in Ruby eliminated off the bat. To test OO code requires mocking to coerce the object into the internal state required to represent the test scenario – how is setting up test data any different? There is a TAP producer for PL/SQL and other tools besides. There are debuggers and profilers too.
  • A stored procedure language is not a fully-featured language. Well, we aren’t trying to write an entire application just in stored procedures! Most dedicated SP languages have all the modern constructs that you would expect, and in Oracle at least, you can use Java Stored Procedures with all the language features OO developers are familiar with, or external procedures in any language. What matters is where the logic is implemented – in one place, close to the data – the actual language is just a detail. PL/SQL compiles to native code and runs in-process with the database; there is no higher-performance architecture than that.
  • I don’t want to have to learn another language. Overlooking for a second this is a huge red flag in any developer (especially one that proposes modifying production apps which might be in other languages anyway!) there is a lot to learn to work in any modern environment: a typical Java shop might have Eclipse, WebLogic, Maven, Hudson, Anthill, Subversion, and a whole plethora of others, that you need to learn before writing a single line of application code. A working knowledge of a very high level SP language is straightforward in comparison, and there will more than likely be a specialist or a DBA around to help you too. Not to mention that developer favourite Hibernate comes with its own query language…

share|improve this answer
add comment

It's a nice question to ask at a website full of DBA's. Hopefully most of the answers will be "pro" towards keeping the database in an ACID state, and thus keeping business logic in the database. :-)

As for my opinion I think you should implement the business logic in both your application(s) and database(s). This approach will cost more time and money but I think it will have a qualitative better business solution as a result.

share|improve this answer
1  
The same logic in two layers? –  dezso Oct 17 '12 at 12:45
    
If you want to create a new customer and you have to store his/her name and a customer number (which always contains 4 numbers) I'd like the application to check if the customer number is valid, before sending a SQL statement to my database (knowing the statement will not pass my businness logic in the database). –  Ruud van de Beeten Oct 17 '12 at 13:04
1  
All businness logic should be implemented in the database (so don't divide 'logic'). Everything you can easly check in the applications (E.g. with regular expressions in Javascript) is less work for the database (in case of invalid input). –  Ruud van de Beeten Oct 17 '12 at 13:15
1  
+1 this is what I do–I just call it "putting the business login in the database and putting convenience checks in the app" –  Jack Douglas Oct 17 '12 at 13:44
1  
You need to have a systematic approach to make this work. Core integrity logic which makes the data always match expectations needs to be done in the database first. Maintaining good communication back to the app from the database of the exceptional conditions and the client being able to communicate them adequately to the user comes next. Then anticipating those before making a trip to the database will be the most duplicative part and necessarily will have to be kept in sync - if you can minimize the need for keeping these in sync you will be best off. –  Cade Roux Oct 17 '12 at 14:06
show 1 more comment

Here is where the meeting of the minds, that is to say, the minds of Developers (DVs) and DBAs, must inevitably happen. Working with Business Logic (BL) and storing such in a database can have an impact that can either glorify or horrify its implementation.

For some RDBMS products, there exists superior libraries/tools/APIs for Business Logic and Object Infrastructures one could quickly learn and employ in their applications. For other RDBMS, no libraries/tools/APIs exist.

In the past, client server apps made the bridge into BL via Stored Procedures (SP). For products such as Oracle and SQL Server, this was done early. As open source databases such as PostgreSQL and MySQL came into being, those using them were at risk of breaking new ground with stored procedures in BL. PostgreSQL matured very quickly in this, since not only stored procedures were implemented but also the ability to craft customer languages also came along. MySQL basically stopped evolving in the world of stored procedures and came in a stripped-down form of a language with many restrictions. Thus, when it comes to BL, you are completely at the mercy of MySQL and its Stored Procedure language.

There only really remains one question: Regardless of the RDBMS, should BL resides in whole or in part in the database ?

Think of the Developer. When things go awry in an application, the debug process will have the Developer hop in and out of a database to follow data chanages that may or may not be correct intermittently. It is like coding a C++ application and calling Assembler code in the middle. You have to switch from source code, classes and structs to interrupts, registers and offsets AND BACK !!! This taking debugging to that same level.

Developers may be able to craft a high speed method of executing BL in conjunction with language configurations (compiler flags for C++, different settings for PHP/Python, etc) via business objects sitting in memory rather than in a database. Some have tried to bridge this ideology for faster runnng code into the database by writing libraries where debugging Stored Procedures and Triggers is well integrated in the Database and seemlessly useable.

Thus, the Developer is challenged to develop, debug, and maintain source code and BL in two langauges.

Now think of the DBA. The DBA wants to keep the Database lean and mean as much as possible in the realm of stored procedures. The DBA may see BL as something external to the Database. Yet, when SQL calls for the data needed for BL, the SQL needs be lean and mean.

Now, for the meeting of the minds !!!

Developer codes SP and uses iteractive methods. DBA looks at the SP. DBA determines that a single SQL statement can replace iteractive methods written by the Developer. Developer sees that the SQL statement suggested by the DBA requires calling other BL-related code or SQL that does not follow normal execution plans of the SQL statement.

In light of this, the configuration, performance tuning, and SP coding becomes a function of the depth and data-intensiveness of BL for data retrieval. The more depth and data-intensiveness the BL, the more Developers and DBA must be on the same page for the amount of data and processing power given to the Database.

CONCLUSION

The manner of data retrieval should be always involving both Developer and DBA camps. Concessions must always be made as to what coding methods and data retrieval paradigms can work together, for both speed and efficiency. If the preparation of data for source code to handle is done only one time before the code gets the data, the DBA should dictate the use of the lean and mean SQL. If the BL is something the DBA is not in tune with, the reins are then in the hands of the Developer. This is why the DBA should see himself/herself and part of the project team and not an island unto himself/herself, while the Developer must let DBA do the fine tuning of the SQL if it does warrant it.

share|improve this answer
add comment

I'm moving my old answer across unedited from programmers.se, as answers seem pretty polarised between sites.

I know I'm in for a world of hurt here, but put business logic in the database because:

  • You can allow business power users direct access to the database and not worry about them screwing it up (or worry less than you would with app-based logic)
  • A power user can create new reports without waiting for a new software release.
  • You can test SP / TRIGGER code in an a copy of the database, just like you do testing app-based logic
  • You can keep the SQL to create sp's and triggers in text files (you should be doing this anyway for table/view code)
  • You can mix and match languages without porting business logic
  • You can make changes to business logic without upgrading every bit of software
  • You audit structure changes the same way you audit database activity - via logging
  • Vastly improved security and fine-grain access control (most app-based logic implementations use their own security model, so the data is far easier to compromise. Reversible password encryption is not uncommon)
  • Database-side user security greatly reduces the damage/theft rogue SQL can do

The cons are: - Developers threatened when users become less reliant on developers for custom reports - Developers need to learn another programming language

Neither of those should matter to a skilled developer.

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.

share|improve this answer
2  
Very good answer –  gbn May 2 '11 at 12:33
3  
The note, especially, suits my heart! :) Thumb up from me. –  Marian May 2 '11 at 16:06
    
* Stored procs/triggers can provide a level of abstraction that allows you to make structural changes in the database without having to change all of the application code. * Not every user of the database will faithfully use your middleware. * C'mon, a foreign key is a business rule!! * Removing all checks/constraints/code from the db means it can't protect itself against inconsistency/corruption. * Every app doesn't call for queue-driven transactionless designs like the one eBay developed after they became successful and could afford to build that. * SQL isn't all that hard, folks. –  Craig Apr 1 at 1:33
add comment

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.

The alternative to implementing this kind of requirement in the database is to let every application programmer reimplement all or part of it 100% correctly, every time they fire up their editor, from the day they first walk through the door until the company goes out of business.

What are the odds?

Isn't this the single biggest "don't repeat yourself" on the planet?

share|improve this answer
add comment

Does the SQL do things like set logic and application oriented results filtering? SQL is a wonderful set manipulation language.

Additionally, as GBN pointed about above, the SQL code is going to almost universally outlive the application code.

While it's true that EF or NHibernate or LinqToSql or whatever will allow you to generate code faster, every programmer worth their performance knows that only optimizing the SQL will optimize the data retrieval. The RDBMS only understands SQL, so you have to make everything into SQL before it's all said and done. (assuming that we can agree that TSQL and PLSQL are still SQL)

share|improve this answer
add comment

Assorted thoughts...

Your database code will outlive your application client technology. Think of ADO.NET -> Linq -> EF as well as assorted ORMs. Whereas you can still run SQL Server 2000 code from last millenium against all of the above client technologies.

You also have the multiple client issue: I have .net, java and Excel. That's 3 sets of application logic.

"Business logic" shouldn't be confused with "data integrity logic". If you do have clients starting transactions and doing assorted checks, that's a lot of db calls and a long transaction.

Application logic doesn't scale for high data volumes. We have 50k rows per second using stored procs. A sister team using Hibernate can't get one per second

share|improve this answer
5  
Well that does sound like they are doing something wrong there GBN :p –  jcolebrand Apr 29 '11 at 23:10
3  
+1 for "We have 50k rows per second using stored procs. A sister team using Hibernate can't get one per second" effing ORMs they aren't useful for anything than simple hello world samples. –  Andrei Rinea Mar 16 '12 at 14:26
1  
@AndreiRinea: The website you are using right now (along with sister-site Stackoverflow, one of the top 100 most-visited sites in the world) uses the O/RM Linq2SQL, the precursor to Microsoft's Entity Framework (EF) –  BlueRaja - Danny Pflughoeft Jan 24 '13 at 23:48
1  
@BlueRaja-DannyPflughoeft : Yes, I know, I've been watching it before even the private beta. However they dropped LINQ2SQL, did you know? They use Dapper now ;) –  Andrei Rinea Jan 25 '13 at 10:11
1  
@BlueRaja-DannyPflughoeft; ADO.NET is a specific data access technology. It is what EF uses "under the covers" to actually access database servers. It is not "the technology for generating webpages...". LINQ was the next natural evolution in .NET once C# had extension methods. @gbn has a very good point, which is that the DB is going to outlive the apps that use it. I've been around long enough to use ODBC, COM, COM+, MSMQ, RDO, ADO, ADO.NET, an assortment of ORMs (always underwhelming in the perf department), etc. The only real long-lived constant has been the databases themselves. –  Craig Mar 13 at 7:25
show 8 more comments

The most important issue is whether any 'layer' above the database thinks that it owns the data. Concurrency and data integrity are problems for which the solution is an RDBMS - some applications are developed as if the database is just their personal bit bucket and of course they end up trying to re-invent the wheel in all sorts of ways, as well as being irreparably broken as soon as some other application accesses the same database

share|improve this answer
    
I think that whoever sponsors the system owns the data - they've paid for it. Also I solve lots of concurrency problems before they hit the database - in many cases it is much easier. –  AlexKuznetsov Jan 22 '13 at 1:26
2  
you are using 'own' in a different sense than I am: my point is that if you 'solve' concurrency problems before they hit the database you have to also ensure that yours are the only applications hitting the database or they have to be solved all over again at that level. I agree with the top voted answer: "Your database code will [likely] outlive your application client technology." –  Jack Douglas Jan 22 '13 at 7:38
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.