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.

This question already has an answer here:

I've worked in some projects where most of the business logic was implemented on the database (mostly through stored procedures). On the other side, I've heard from some fellow programmers that this is a bad practice ("Databases are there to store data. Applications are there to do the rest").

Which of these approaches is the generally better?

The pros of implementing business logic in the DB I can think of are:

  • Centralization of business logic;
  • Independency of application type, programming language, OS, etc;
  • Databases are less prone to technology migration or big refactorings (AFAIK);
  • No rework on application technology migration (e.g.: .NET to Java, Perl to Python, etc).

The cons:

  • SQL is less productive and more complex for business logic programming, due to the lack of libraries and language constructs the most application-oriented languages offer;
  • More difficult (if possible at all) code reuse through libraries;
  • Less productive IDEs.

Note: The databases I'm talking about are relational, popular databases like SQL Server, Oracle, MySql etc.

Thanks!

share|improve this question

We're looking for long answers that provide some explanation and context. Don't just give a one-line answer; explain why your answer is right, ideally with citations. Answers that don't include explanations may be removed.

marked as duplicate by gnat, Kilian Foth, thorsten müller, Martijn Pieters, Jalayn Apr 10 '13 at 12:38

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

3  
You might find the answer to this question useful. –  Blrfl Apr 9 '13 at 15:14
6  
This argument has already been debated exhaustively. What more could we meaningfully add to the conversation here? –  Robert Harvey Apr 9 '13 at 15:18
2  
@gnat: Not even close. –  Robert Harvey Apr 9 '13 at 23:57
    
@RobertHarvey take a look at the question in suggested dupe: "applications with the business logic pushed down into the database layer, only the user interface is outside the database.... whether there are any contemporary best practices if one was to write a database application in this manner?" –  gnat Apr 10 '13 at 4:45
1  
similar to programmers.stackexchange.com/questions/158534/… –  minusSeven Apr 10 '13 at 11:07

6 Answers 6

up vote 20 down vote accepted

Business logic doesn't go into the database

If we're talking about multi-tier applications, it seems pretty clear that business logic, the kind of intelligence that runs a particular enterprise, belongs in the Business Logic Layer, not in the Data Access Layer.

Databases do a few things really well:

  1. They store and retrieve data
  2. They establish and enforce relationships between different data entities
  3. They provide the means to query the data for answers
  4. They provide performance optimizations.
  5. They provide access control

Now, of course, you can codify all sorts of things in a database that pertain to your business concerns, things like tax rates, discounts, operation codes, categories and so forth. But the business action that is taken on that data is not generally coded into the database, for all sorts of reasons already mentioned by others, although an action can be chosen in the database and executed elsewhere.

And of course, there may be things that are performed in a database for performance and other reasons:

  1. Closing out an accounting period
  2. Number crunching
  3. Nightly batch processes
  4. Fail-over

Naturally, nothing is engraved in stone. Stored Procedures are suitable for a wide array of tasks simply because they live on the database server and have certain strengths and advantages.

Stored Procedures Everywhere

There's a certain allure to coding all of your data storage, management and retrieval tasks in stored procedures, and simply consuming the resulting data services. You certainly would benefit from the maximum possible performance and security optimizations that the database server could provide, and that's no small thing.

But what do you risk?

  1. Vendor lock-in
  2. The need for workers with special skill sets
  3. Spartan programming tools, overall
  4. Extremely tight software coupling
  5. No separation of concerns

And of course, if you need a web service (which is probably where this is all heading, anyway), you're still going to have to build that.

So what is typical practice?

I would say that a typical, modern approach is to use an Object-Relational Mapper (such as Entity Framework) to create classes that model your tables. You can then speak to your database through a repository that returns collections of objects, a situation that is very familiar to any competent software developer. The ORM dynamically generates SQL corresponding to your data model and the information requested, which the database server then processes to return query results.

How well does this work? Very well, and much more rapidly than writing stored procedures and views. This generally covers about 80% of your data access requirements, mostly CRUD. What covers the other 20%? You guessed it: stored procedures, which all of the major ORMs support directly.

Can you write a code generator that does the same thing as an ORM, but with stored procedures? Sure you can. But ORMs are generally vendor-independent, well-understood by everyone, and better supported.

share|improve this answer
    
Thank you for your great answer, @Robert Harvey. But I was thinking about the "vendor lock-in" argument: insn't using a particular technology (say, the .NET or Java stack) to build an application also a vendor lock-in? Or are there advantages of an app-oriented stack vendor lock-in versus a DB one? –  Raphael Apr 10 '13 at 12:58
    
Entity Framework supports several different backend databases. –  Robert Harvey Apr 10 '13 at 14:48

There's a middle ground that you need to find. I've seen scary projects where the programmers use the database as nothing more than an overpriced key/value store. I've seen others where the programmers fail to use foreign keys & indices. On the other end of the spectrum, I've seen projects where most if not all of the business logic is implemented in database code.

As you've noted, T-SQL (or its equivalent in other popular RDBMSs) is not exactly the best place to be coding complex business logic.

I try to build a reasonably decent data model, use features of the database to protect my assumptions about that model (i.e., FKs and constraints), and use database code sparingly. The database code is useful when you need something (i.e., a sum) that the database is very good at doing and can spare you from moving a zillion records over the wire when you don't need them.

share|improve this answer
1  
Using the database as an "overpriced" key/value store is a perfectly valid technique, as the legions of NoSQL practitioners will attest. –  Robert Harvey Apr 9 '13 at 15:28
    
@RobertHarvey You're obviously correct, but somehow my gut continues to insist that there must be a simpler/cheaper/faster solution than a database if all you need is a key/value store. I need to learn more about NoSQL. –  Dan Pichelman Apr 9 '13 at 15:47
2  
I don't see using stored procedures as a cure for a poorly designed database. –  JeffO Apr 9 '13 at 20:39
1  
@RobertHarvey, I readed "overpriced key/value store" literally. Paing an Oracle or SQL Server license for something like that, when there are options like MongoDB available for free, seems like wasting money. –  Raphael Apr 10 '13 at 13:34

I am a strong believer in keeping business logic out of the database as much as possible. However, as my company's performance developer, I appreciate that sometimes it's necessary to achieve good performance. But I think it is necessary far less often than people claim.

I dispute your pros and cons.

You claim that it centralizes your business logic. On the contrary, I think it decentralizes it. In a product that I currently work on, we use stored procedure for a lot of our business logic. Many of our performance issues come from calling functions repeatedly. For instance

select <whatever>
from group g
where fn_invoker_has_access_to_group(g.group_id)

Problem with this approach is that it generally (there may be cases where this is false) forces the database to run your function N times, once per row. Sometimes that function is expensive. Some databases support function indexes. But you can't index every possible function against every possible input. Or can you?

A common solution to the above problem is to extract the logic from the function and merge it into the query. Now you have broken encapsulation and duplicated logic.

Another issue I see is calling stored procedures in a loop because there is no way to join or intersect stored proc result sets.

declare some_cursor
while some_cursor has rows
    exec some_other_proc
end

If you pull the code from the nested proc out, then you again decentralize. Therefore, you are forced to choose between encapsulation and performance.

In general, I find that databases are bad at:

  1. Computation
  2. Iteration (they are optimized for set operations)
  3. Load balancing
  4. Parsing

Databases are good at:

  1. Locking and unlocking
  2. Maintaining data and their relationships
  3. Ensuring integrity

By taking expensive operations like loops and string parsing and keeping them in your app tier, you can horizontally scale your application to get better performance. Adding multiple app servers behind a load balancer is usually far cheaper than setting up database replication.

You are correct, however, that it decouples your business logic from your application's programming language, but I don't see why that is an advantage. If you have a Java app, then you have a Java app. Converting a bunch of Java code into stored procedures doesn't change the fact that you have a Java app.

My preference is to keep database code focused on persistence. How do you create a new widget? You must insert into 3 tables and they must be in a transaction. That belongs in a stored procedure.

Defining what can be done to a widget and the business rules for finding widgets belongs in your application.

share|improve this answer
1  
In SQL server only poorly written sps need to be called in a loop, you can send it sets of data in a parameter and do a set-based process. –  HLGEM Apr 9 '13 at 21:27
1  
SQL Server will generate a sub-optimal query plan whenever there's a UDF in a WHERE clause. –  Jim G. Apr 9 '13 at 23:28
    
Looks like your performance problem is not the fault of logic in database vs app.. it's just poorly written and architected. That problem will follow you in the ORM world just the same. ORMs can be a real headache outside of CRUD operations. If your system is data heavy, reporting type of system, please use caution. –  sam yi Jul 15 at 18:06
    
That is true. Most of our performance issues are simply due to poorly written code and over-complex architecture. But I still believe that we put the wrong type of work into our databases. Coding as much as possible into the database has caused us to do things a database is not good at. –  Brandon Jul 15 at 19:24

I have worked in 2 different companies that had different vision on the subject.

My personal suggestion would be to use Stored Procedures when execution time is important (performance). Since Stored Procedure are compiled, if you have a complex logic to query the data, it's better to keep that on the database itself. Also, it will only send the final data to your program at the end.

Otherwise, I think the logic of a program should always be in the software itself. Why? Because a program needs to be testable and I don't think there is an easy way to unit test stored procedure. Don't forget, a program that is not tested is a bad program.

So use Stored Procedure with caution, when it's needed.

share|improve this answer
1  
Stored procedures are unit testable. See here for some techniques. –  Robert Harvey Apr 9 '13 at 15:23
3  
afaik, an unit test never use database or file. So technically, "unit testing" a stored procedure is not unit testing and it will be slow as hell. A unit test suite should be run in seconds (or maybe minutes with very big application) at any time during development. –  Jean-François Côté Apr 9 '13 at 15:28
1  
The OP was talking about "business logic" and business logic should be unit tested. By putting it in a stored procedure, you mix it with database query which slow the whole process. Like I said, you can use Stored Procedure (it's not a crime) but it will blur the line between the business logic and database layer which is bad. Use it with care :) –  Jean-François Côté Apr 9 '13 at 15:34
1  
If you create the db and necessary objects, the sp, test, and then tear it down afterwards, it's a unit test. It tests a unit of work. –  Tony Hopkinson Apr 9 '13 at 20:39
1  
Hasn't the performance gains with stored procedures myth been debunked? –  JeffO Apr 9 '13 at 20:50

If your business logic involves set operations, most likely a good place for it is in the database because database systems are really good at performing set operations.

http://en.wikipedia.org/wiki/Set_operations_(SQL)

If the business logic involves some sort of calculation it probably belongs outside of the database/store procedure since databases are not really designed for looping and calculating.

Although these are not hard and fast rules, its a good starting point.

share|improve this answer

There is no one right answer to this. It depends on what you use the database for. In an ENterprise application you ned thelogin the database through foreignkeys and constraints and triggers, etc because it is the only place where all possible applications share code. Further, putting the reuired logic in code generally mean s the database is inconsistent and the data is of poor quality. That may seem trivial to an application devloper who is only concerend with how the GUI works, but I assure you that the people trying to use the data in compliance reports find it very annoying and costly when they get billion dollar fines for having data that didn't follow the rules correctly.

In a non-regulartory environment when you don;t care as much about the whole set of records and only one or two applciations hit the database, maybe you can get away with keeping it all in the application.

share|improve this answer

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