77

As a software engineer, I have a strong bias towards writing business logic in the application layer, while typically relying on the database for little more than CRUD (Create Retrieve Update and Delete) operations. On the other hand, I have run across applications (typically older ones) where a large amount of the business logic was written in stored procedures, so there are people out there that prefer to write business logic in the database layer.

For the people that have and/or enjoy written/writing business logic in a stored procedure, what were/are your reasons for using this method?

senfo
  • 28,488
  • 15
  • 76
  • 106
  • 15
    I'm a DBA. DATA logic should be in the database. The way you manipulate the database structures to perform the activities required by the functionality SHOULD BE held in the database. When you join up these activities into a larger series of processes (business logic) then THIS can be consolidated in the application. – Guy Sep 25 '09 at 14:21
  • IT managers are the problem. They misuse DBAs by expecting them to be part of system development. DBAs just use the tool at hand. I'm a full stack developer. I have cleaned "data logic" for years and convinced my employer to forbid stored procedures for business processes, after extracting Excel spreadsheet work from hundreds of queries, one of which filtered FINANCIAL DATA. Processing only belongs in applications where an interface provides control and information to a user. In other words, SPs are for database work not user data entry. – RBJ Apr 17 '20 at 17:10

16 Answers16

49

I try to seriously limit my business logic in the DB to only procs that have to do alot of querying and updating to perform a single application operation. Some may argue that even that should be in the app, but I like to keep the IO down if I can.

Databases are great for CRUD but if they get bloated with logic:

  1. It becomes confusing where the logic is,
  2. Typically databases are a silo and do not scale horizontally nearly as well as the app servers.
  3. t_sql/PLsql is hard to read and procedural in nature
  4. You forfeit all of the benefits of OOAD.
Matt Wrock
  • 6,590
  • 29
  • 23
  • 4
    T-SQL and other SQL variants can be hard to read, but I would consider them declarative instead of procedural--cursors being one exception. – Scott Lawrence Sep 24 '09 at 19:34
  • 2
    How do you handle transactions then? For example: Lets say that i need to create a new Order in my system. The business scenario in this case would require the system to: document the order, send an email to the customer (add a row to emailQueue table), and - (this is the tricky part) if order.type=1 -> update table1 else update table2. This all has to be in a single transaction. Would you right this in a Business Logic layer and handle the transactions in code or would you move all this to the database? – Uri Abramson Nov 21 '13 at 12:40
  • @UriAbramson I would not want to wait on each of these processes to complete. So, I would probably use a service bus here and have a transnational queue for each. I would not put this in the database. Databases should be for data persistence only. My opinion. – Scott Adams Nov 26 '13 at 20:45
  • 17
    "t_sql/PLsql is hard to read" - Based on what? It's just code. I might think Objective-C is hard to read, but another person might think it's the most intuitive language since sliced bread. Does that mean Objective-C is hard to read? Not at all. The language is just a tool, it's just syntax. You can write bad PL/SQL code, just like you can write bad C# code. But it's not the language's fault in either case, it's the person's fault who wrote the code. Whether or not a language is "hard to read" mainly depends on your familiarity with it. – dcp Jan 23 '14 at 17:42
  • That's very fair. Been 4 years since posting this answer. The biggest thing that sticks for me is the danger of making the DB your application server. The "hard to read" issue not so huge. – Matt Wrock Jan 23 '14 at 18:47
  • 3
    I don't necessarily agree with the answer. How do you define logic here? Is it just a bunch of calculations and derivations? or the process that intertwines it? The application is a mere presentation layer and the days of fat clients with heavy application side logic are gone. Furthermore, single bulk operations invoked from Application may suffer severe performance issues either during the logic or during DB query/Inserts/Updates/Deletes and tuning a DB side would be far more easier and efficient than tuning Application side logic. – Anjan Biswas Aug 08 '14 at 08:30
  • 1
    In addition to that, apps are moving more towards Asynchronous processing, wherein a user does operation A in the UI and moves on with operation B but operation A still runs asynchronously in the back ground without making the user wait for itself to finish. An efficient implementation of such an Asynchronous behavior is virtually impossible with total Business logic coded solely at the Application level. – Anjan Biswas Aug 08 '14 at 08:38
  • 1
    @dcp So what you are saying is that for example brainfuck is as easy to read as java when you get used to it? – Kaur Kase Jun 21 '17 at 10:57
  • @Kaur Kase - I'm saying the language is a tool, and it's how well you use that tool that matters. "Easy to read" is more a matter of being familiar with the language and its syntax, and that comes with time. The most important takeaway though is that a bad programmer will write bad code in any language. You can put a bad driver in a Ferrari and they will still be a bad driver. It's the same idea with programming. Writing good code is a function of the programmer, not the language. – dcp Jun 21 '17 at 13:49
38

To the maximum extent possible, keep your business logic in the environment that is the most testable and debuggable. There are some valid reasons for storing business logic in the database in other people's existing answers, but they are almost always far outweighed by this.

  • that's true. I have also worked on very complex application and nested DB which more than half of business logic is on Database. If I wanted to put the conditions and logics on class or lib it would have been nightmare and it give access to end user to do almost access. other things e.g. Auth, ACL, comparisons,... – Fury Jan 14 '14 at 10:11
  • 3
    And the prize for most succinct answer to this question goes to ... – Jason Glover Mar 03 '14 at 23:13
  • 2
    Let's see testing and debugging is maybe 1% of 1% of the time associated with a database query's life, so yes the's optimize for that. What a silly notion. Maybe just maybe if you worked in the database all the time, you wouldn't find it so hard to debug and test. I certainly don;t. Just because something is not like what you are used to is no reason not to use it if it is the best tool for the job. – HLGEM Aug 27 '15 at 16:53
  • 2
    "_Maybe just maybe if you worked in the database all the time ..._" - sounds like you're admitting that you have only one tool and it's a hammer. Maybe just maybe when building logically complex systems some people prefer to ensure behavioral correctness over execution speed. "I have no idea if it's correct, but it's fast!" –  Mar 14 '17 at 04:09
21

Limiting the business logic to the application layer is short-sighted at best. Experienced professional database designers rarely allow it on their systems. Database need to have constraints and triggers and stored procs to help define how the data from any source will go into it.

If the database is to maintain its integrity and to ensure that all sources of new data or data changes follow the rules, the database is the place to put the required logic. Putting it the application layer is a data nightmare waiting to happen. Databases do not get information just from one application. Business logic in the application is often unintentionally bypassed by imports (assume you got a new customer who wanted their old historical data imported to your system or a large number of target records, no one is going to enter a million possible targets through the interface, it will happen in an import.) It is also bypassed by changes made through the query window to fix one-time issues (things like increasing the price of all products by 10%). If you have application layer logic that should have been applied to the data change, it won't be. Now it's ok to put it in the application layer as well, no sense sending bad data to the database and wasting network bandwidth, but to fail to put it in the database will sooner or later cause data problems.

Another reason to keep all of this in the database has to to with the possibility of users committing fraud. If you put all your logic in the application layer, then you must grant the users access directly to the tables. If you encapsulate all your logic in stored procs, they can be limited to doing only what the stored procs allow and not anything else. I would not consider allowing any kind of access by users to a database that stores financial records or personal information (such as health records) as I would not allow anyone except a couple of dbas to directly access the production records in any way shape or form. More fraud is committed than many developers realize and almost none of them consider the possibility in their design.

If you need to import large amount of data, going through a data access layer could slow down the import to a crawl becasue it doesn't take advanatge of the set-based operations that databases are designed to handle.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • 31
    Yes, data can come from more than one application, but this doesn't mean you have to put the logic in the database. What's wrong with having another layer above the database that all applications must pass through? This is the reason behind n-tier system architecture. If you wanted to import a large amount as data why assume that this will be done direct to the database? A bulk import application could be developed using a service layer, which would then funnel that data through the same business logic layer... – Andy McCluggage Sep 25 '09 at 14:29
  • 5
    ...Having multiple layer means that you can avoid a single layer doing too much. Databases are for data persistence only. – Andy McCluggage Sep 25 '09 at 14:30
  • 12
    All I can say is you have never spent months fixing bad data from poor designs like that. I have. Databases are not for data persiostence only. – HLGEM Sep 25 '09 at 15:09
  • 12
    The is nothing inherently poor about a properly structured, multi tiered design. You have clearly, like most, dealt with some specifically poor designs. I’ve been there too, and it has never lead to me think it was a good idea to put all business logic in the database. In fact, the biggest, most unfathomable messes I have seen are where this HAS been done. – Andy McCluggage Sep 30 '09 at 14:57
  • 1
    +1 on the point about SPs allowing for better security. – Rob Garrison Oct 06 '09 at 17:38
  • 8
    @HLGEM - I really liked your answer. I totally agree that business rules need to be in the database. As to Andy's point about writing the "middle tier", what happens when that middle tier language becomes obsolete? For example, what if the middle tier was written in VB6 8 years ago? What are you going to do now to get it into a more maintainable language? Rewrite it all? If you had put the business rules in the database, they'd be there and could easily be maintained for years and years. Nobody was using Java 20 years ago, but plenty of people were using Oracle. – dcp Dec 12 '13 at 19:45
  • 1
    Ironically, this exact scenario on the business rules being written in VB6 is happening on a project I'm aware of at one particular company. So guess what they're doing now? Rewriting all the business rules in Drools (a Java rules based engine). If they had put those rules in the database to begin with, then all they'd be re-writing is the front end code, not the business rules, which is the most important part. Tom Kyte has an excellent post on this: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2143974700346554115 – dcp Dec 12 '13 at 19:48
  • 7
    @dcp And what about if your database becomes obsolete? Same argument that you made against obsolete middle tier languages... Granted that database languages last longer than many programming languages, sticking with something just because you don't want to change anything is just losing out on all the innovations that have been produced through the years as you huddle in that corner. My main gripe with database business logic is that it's almost never DRY, so a single change will require you to go through everything to make sure it all works... – Populus Jan 23 '14 at 16:50
  • 1
    @Populus - I've used Oracle since 1992, started using Sybase (now SQL Server) since about that time. DB2 has been around even longer than that. Your profile indicates you answer a lot of PHP questions. Were you writing PHP code in 1992? I doubt it, because it didn't exist back then. Neither did C#, Java, Objective-C or most of the popular languages of the day. But Oracle existed, as did PL/SQL. The point is, if you had put your business logic in PL/SQL in 1992, that logic would still work today, and it wouldn't be obsolete. What if you put that same business logic in Powerbuilder? – dcp Jan 23 '14 at 17:23
  • 1
    @Populus - I wrote an article several years back where I discuss this topic in some detail. see best practices section of the article here: http://goo.gl/Y9wzMn The quote by Tom Kyte sums it up best: "What has persevered for over 20 years in the relational world (and probably most of the object implementations as well) is the database itself. The front ends to the data change almost yearly, and as they do, the applications that have all of the security (and business logic) built inside themselves, not in the database, become obstacles, roadblocks to future progress" – dcp Jan 23 '14 at 17:25
  • 4
    This is one of the Usenet holy wars. Splitting your logic between the DB and App often ends up confusing people (the app coder can't see the magic happening in the DB, and be sure there are magicians amongst us) and making deployments harder. It can, in theory, be very good. It is in practise an awful thing to support and maintain. – Bwooce Apr 07 '14 at 06:08
  • "all sources of new data or data changes follow the rules" - that makes sense if you permit or require multiple different consumers of the database. In most cases, when you expose your database through some middle-tier API only, it is OK to move the logic to the API. Also, it is a nightmare to create a modern ORM-based consumer of a database, if a simple UPDATE operation can blow your ORM tool with unexpected side effects of updating / inserting / deleting other records, returning validation errors etc. etc. – JustAMartin Nov 29 '19 at 13:48
17

Your usage of the term "business logic" is rather vague.

It can be interpreted to mean to include the enforcement of constraints on the data (aka 'business rules'). Enforcement of these unequivocally belongs in the dbms, period.

It can also be interpreted to mean to include things like "if a new customer arrives, then within a week we send him a welcome letter." Trying to push stuff like this in the data layer is probably a big mistake. In such cases, the driver for "create a new welcome letter" should probably be the application that also triggers the new customer row insertion. Imagine every new database row insertion triggering a new welcome letter, and then suddenly we take over another company and we must integrate that company's customers in our own database ... Ouch.

Erwin Smout
  • 18,113
  • 4
  • 33
  • 52
14

We do a lot of processing in the DB tier, where appropriate. There's a lot of operations you wouldn't want to pull back large datasets to the app tier to do analysis on. It's also an easier deployment for us -- a single point vs. updating applications at all install points. But a lot depends on your application and what it does; there's no single good answer here.

Joe
  • 41,484
  • 20
  • 104
  • 125
7

I'd say if 'business-logic' means application flow, user control, timed operations and generally 'doing-business-stuff' then it should be in the application layer. But if it means making sure that no matter how you dig around in the data, it always makes sense and is a sensible, non-self-conflicting whole, then the checks to enforce those rules go in the DB, absolutely, no questions. There are always many ways to push data into the DB and manipulate it once its there. Not all those ways have 'business-logic' built in to them. You will find a SQL session into a DB through a DOS window on a support call at 3am is very liberal in what it allows for example! If the logic isn't in the DB to make sure that ALL data changes make sense, you can bet for sure that the data will get very, very screwed up over time. And since a system is only as valuable as the data it holds, that makes for a much lower return on investment.

Simon Oliver
  • 71
  • 1
  • 1
7

On a couple of ocassions I have put 'logic' in sprocs because the CRUD might be happening in more than one place. By 'logic' I would have to say it is not really business logic but more 'integrity logic'. It might be the same - some cleanup might be necessary if something gets deleted or updated in a certain way, and if that delete or update could happen from more than one tool with different code-bases it made sense to put it in the proc they all used.

In addition, sometimes the 'business logic line' is pretty blurry. Take reports for example - they may rely on stored procedures or views that encapsulate 'smarts' about what the schema means to the business. How often have you seen CASE statements and the like that 'do things' based on column values or other critieria? Could be construed as business logic and yet it probably does belong in the DB where it can be optimized, etc.

n8wrl
  • 19,439
  • 4
  • 63
  • 103
6

Two good reasons for putting the business logic in the database are:

  • It secures your logic and data against additional applications that may access the database that don't implement similar logic.
  • Database designs usually outlive the application layer and it reduces the work necessary when you move to new technologies on the client side.
dpbradley
  • 11,645
  • 31
  • 34
  • 3
    If you have some kind of data-integrity or other business logic in the database, do you completely leave it out of your "Business Logic" layer? Or do you duplicate it? (For example, checking that a value is valid before entering in the DB, even though the DB itself won't let you enter it?) – Edan Maor Sep 24 '09 at 19:30
  • 38
    A database is for data persistence. If you did as you recommend, you are building for failure. Additional applications that need the same data should be controlled in how they access it and not by the data level itself. Too not do so and say, "Oh, the database controls that" is to open yourself up to huge oversights. Furthermore, database designs outlive applications because they are too difficult to change. This is also why you want to put as little into them as possible. The more logic you bundle with your data, the more you are going to have to work around as things change. – Ty. Sep 24 '09 at 19:51
  • 3
    @Edan - duplicating the logic can make sense in certain cases where it improves performance by saving network round trips to/from the back end. You still have the safety net at the back end – dpbradley Sep 24 '09 at 19:56
  • 8
    @Ty - We could probably both point to examples from experience that support our opinions, but once you get up to sufficiently complex applications with hundreds of tables and high transactions rates that are accessed by dozens of applications that need to share data, I'll take my logic on the back end. – dpbradley Sep 24 '09 at 20:09
  • @dpbradley That is a point you should edit into your post. Performance. Depending on the nature of the data and the way it is being accessed, it can sometimes be necessary to do more of the logic on the database server for performance concerns. However, a highly complex system being accessed by dozens of applications should be doing so consistently. It is very hard to ensure those concerns at a database level and I would be very wary of trying to without some very good reasons. – Ty. Sep 24 '09 at 20:39
  • Both those statements are just completely wrong. You have obviously missed the point. – Jack Dec 02 '09 at 03:30
  • 2
    @Jack - care to explain? Please post your own answer to the question if you have a counterpoint. – dpbradley Dec 02 '09 at 12:29
5

You often find business logic at the database layer because it can often be faster to make a change and deploy. I think often the best intentions are not to put the logic there but because of the ease of deployment it ends up there.

Gratzy
  • 9,164
  • 4
  • 30
  • 45
4

The primary reason I would put BL in stored procs in the past is that transactions were easier in the database.

If deployments are difficult for your app and you don't have an app-server, changing the BL in stored procedures is the most effective way to deploy a change.

Austin Salonen
  • 49,173
  • 15
  • 109
  • 139
  • 1
    a few people in this thread have commented that it is easier to update/deploy changes to the DB. I don't like this argument. If it is hard to deploy your app, the answer is not to move the logic you change often to the DB, it is to fix your deployment mechanism! I work in a major financial institution and have many financial apps both desktop and web. Everyone of them can be deployed in a heartbeat without any noticeable downtime. Techniques like blue/green releases are the way to go, or apps that check for updates on start up. – Mark Jones Jan 08 '15 at 14:53
4

I work for a financial type company where certain rules are applied by states, and these rules and their calculations are subject to change almost daily if not surely weekly. That being the case, it made more sense to move parts of the logic dealing with calculations to the database; where a change can be tested and applied without having to recompile and redistibute an application, which is impossible to do daily without disrupting business. The stored proc is tested, approved, applied and the end user is none the wiser. With the move to web based applications, the reliance on moving the logic to the database is less but still present. Even web apps (depending on the language) must be compiled and published to the site which could cause downtime.

Yaniv C
  • 41
  • 1
3

I'm in a team to build-up and maintain a rather large financial system, and I find no way put the logic into the application layer for action that affect to or get constraints from dozens of thousand records.

Beside the performance issue, should errors happen, rectifying a stored procedures is much faster than debugging the application, fixing, recompiling, redeploying the code with longer downtime

Ben
  • 379
  • 1
  • 5
  • 14
  • That is a very baseless and anecdotal claim. – senfo Dec 29 '12 at 16:55
  • it's funny that: looking back my comment a few years ago, I'm still working on the old system. And I'm on the way to rebuild a new version of it now. However, I'm still in the same situation. I don't mean that there's no way to bring the logic execution to application layer. What I mean is I don't know how to for my case, maybe because of my limitation. I'm very pleased if someone can teach me. – Ben Feb 16 '15 at 07:34
  • My basic demand is: update one row only, but because of many constraints, the validation execution will go through many thousands of rows, which are filtered from a tables of tens of millions records. After that many thousands of rows will be updated. It's unlimited tree level structure – Ben Feb 16 '15 at 07:38
3

Sometimes business logic is too slow to run on the app layer. This is especially true on on older systems where client power and bandwidth was more limited.

Byron Whitlock
  • 52,691
  • 28
  • 123
  • 168
3

The main reason for using the database to do the work is that you have a single point of control. Often, app developers re-use or rewrite code fragments in different parts of the application. Even assuming that these all work exactly the same way (which is doubtful), when the business logic changes, the app needs to be reviewed, recoded, recompiled. Unless the parameters change, this would not be necessary where the business logic is stored only in the database.

Rap
  • 51
  • 1
3

My preference is to keep any complicated business logic out of the database, simply for maintenance purposes. If I get a call at 2 o'clock in the morning I would rather debug my application code than try to step through database scripts.

Mr. Will
  • 2,298
  • 3
  • 21
  • 27
2

I think Specially for older applications which i working on (Banking) where the Bussiness logic is huge, it's almost next to impossible to perform all these business logic in application layer, and also It's a big performenance hit when we put these logic in Application layer where the number of fetch to the database is more, results in more resource utilization(more java objects if it's done in java layer) and network issues and forget abt performenance.

santosh
  • 21
  • 1