Business Logic: Database or Application Layer

Question

The age old question. Where should you put your business logic, in the database as stored procedures ( or packages ), or in the application/middle tier? And more importantly, Why?

Assume database independence is not a goal.

Bremser

You probably ought to add "subjective" tag to this. – Makeshift

Martin Fowler's musing on the subject: martinfowler.com/articles/dblogic.html#DomainModel – Syrinx

@NathanLong, He's got a bad habit of making things longer than they need to be. – Ridden

Answer 1

Put enough of the business logic in the database to ensure that the data is consistent and correct.

But don't fear having to duplicate some of this logic at another level to enhance the user experience.

Rectum

IMHO one should make the database as smart about enforcing data integrity as is feasible to do. Applications and DB's tend to live in a M:M relationship and you cannot necessarily assume all updates are going to go through your data access layer. – Wedgwood

+1 to duplicating the business logic. Or rather, don't sweat the fact that you may have to do so, if it enhances the UX. It may also help performance on distributed systems. – Canker

The 'business logic' that belongs in the database is, first and foremost, the design of the tables. Hopefully this accurately represents the data needs of said business. That being said, DO NOT duplicate business functionality. I can say, from a LOT of experience, that systems who's original developer has moved on will be lacking in documentation - and if you change some logic, you don't want to be surprised that you had to change it in two or more places. – Sensillum

I'd also be very wary of duplicating logic. It's not for nothing that DRY is a central point of agile methods. But I'm not convinced it's really necessary. And I wouldn't count preventing constraing violations in the UI "duplicate logic"... – Recommit

+1 ConcernedOfTunbridgeWells: that's a great reason to enforce data integrity at the database layer! – Rosco

Tom Kyte has a good thread on this subject: asktom.oracle.com/pls/apex/… – Opsonize

Answer 2

Maintainability of your code is always a big concern when determining where business logic should go.

Integrated debugging tools and more powerful IDEs generally make maintaining middle tier code easier than the same code in a stored procedure. Unless there is a real reason otherwise, you should start with business logic in your middle tier/application and not in stored procedures.

However when you come to reporting and data mining/searching, stored procedures can often a better choice. This is thanks to the power of the databases aggregation/filtering capabilities and the fact you are keeping processing very close the the source of the data. But this may not be what most consider classic business logic anyway.

Inexpedient

SELECT SUM(x) runs just as fast in a Stored Procedure as it does in the application tier. Not sure that the 3rd paragraph of your answer is really necessary. – Makeshift

On SUM, you're right of course, however I was really comparing using the DB functions against using Java/C# etc to manually aggregate. Also, in situations where multiple network roundtrips are costly, stored procs certainly have their place. – Inexpedient

When another application needed access to business functionality that I had written in a middle-tier (VB.NET), I made a stored procedure for it to call. I then commented out the VB middle-tier code, replacing it with a call to the procedure. It's a lot easier to debug in an IDE but sometimes you have other concerns. – Sensillum

+1 When aggregating / reporting on large datasets, sprocs can be a lot faster, because the dataset can be processed locally on the server, instead of being shipped to the middle tier. But that's a performance optimization that can wait until you've found it necessary. – Recommit

@Sleske: I agree, you shouldn't complex your design in favor of a performance issue that haven't arise. I made once. my mistake, I won't again. – Vivacious

If the data is in memory it will be a lot faster then using IO to talk to the server period .. use caching for repeated sets so you dont even hit the db .. And you dont need to use brute force in C#/Java we also have dictionaries , hashes , sorted lists , binary searches , trees etc Which are orders of magnitude faster than any db server. Not to mention by using the same sets you can simplify your db logic / code a lot. – Hod

Answer 3

For very simple cases you can put your business logic in stored procedures. Usually even the simple cases tend to get complicated over time. Here are the reasons I don't put business logic in the database:

Putting the business logic in the database tightly couples it to the technical implementation of the database. Changing a table will cause you to change a lot of the stored procedures again causing a lot of extra bugs and extra testing.

Usually the UI depends on business logic for things like validation. Putting these things in the database will cause tight coupling between the database and the UI or in different cases duplicates the validation logic between those two.

It will get hard to have multiple applications work on the same database. Changes for one aplication will cause others to break. This can quickly turn into a maintenance nightmare. So it doesn't really scale.

More practically SQL isn't a good language to implement business logic in an understandable way. SQL is great for set based operations but it misses constructs for "programming in the large" it's hard to maintain big amounts of stored procedures. Modern OO languages are better suited and more flexible for this.

This doesn't mean you can't use stored procs and views. I think it sometimes is a good idea to put an extra layer of stored procedures and views between the tables and application(s) to decouple the two. That way you can change the layout of the database without changing external interface allowing you to refactor the database independently.

Oppugn

Answer 4

It's really up to you, as long as you're consistent.

One good reason to put it in your database layer: if you are fairly sure that your clients will never ever change their database back-end.

One good reason to put it in the application layer: if you are targeting multiple persistence technologies for your application.

You should also take into account core competencies. Are your developers mainly application layer developers, or are they primarily DBA-types?

Zelazny

Answer 5

While there is no one right answer - it depends on the project in question, I would recommend the approach advocated in "Domain Driven Design" by Eric Evans. In this approach the business logic is isolated in its own layer - the domain layer - which sits on top of the infrastructure layer(s) - which could include your database code, and below the application layer, which sends the requests into the domain layer for fulfilment and listens for confirmation of their completion, effectively driving the application.

This way, the business logic is captured in a model which can be discussed with those who understand the business aside from technical issues, and it should make it easier to isolate changes in the business rules themselves, the technical implementation issues, and the flow of the application which interacts with the business (domain) model.

I recommend reading the above book if you get the chance as it is quite good at explaining how this pure ideal can actually be approximated in the real world of real code and projects.

Sclater

I have read the book, but did not have a chances to use it practically. Have you tried it? – Mezzanine

Answer 6

While there are certainly benefits to have the business logic on the application layer, I'd like to point out that the languages/frameworks seem to change more frequently then the databases.

Some of the systems that I support, went through the following UIs in the last 10-15 years: Oracle Forms/Visual Basic/Perl CGI/ ASP/Java Servlet. The one thing that didn't change - the relational database and stored procedures.

Claire

Good point; however that might simply be because changing DBMS is so painful, and one major reason for this is the use of sprocs in vendor-specific languages... – Recommit

Answer 7

The only thing that goes in a database is data.

Stored procedures are a maintenance nightmare. They aren't data and they don't belong in the database. The endless coordination between developers and DBA's is little more than organizational friction.

It's hard to keep good version control over stored procedures. The code outside the database is really easy to install -- when you think you've got the wrong version you just do an SVN UP (maybe an install) and your application's back to a known state. You have environment variables, directory links, and lots of environment control over the application.

You can, with simple PATH manipulations, have variant software available for different situations (training, test, QA, production, customer-specific enhancements, etc., etc.)

The code inside the database, however, is much harder to manage. There's no proper environment -- no "PATH", directory links or other environment variables -- to provide any usable control over what software's being used; you have a permanent, globally bound set of application software stuck in the database, married to the data.

Triggers are even worse. They're both a maintenance and a debugging nightmare. I don't see what problem they solve; they seem to be a way of working around badly-designed applications where someone couldn't be bothered to use the available classes (or function libraries) correctly.

While some folks find the performance argument compelling, I still haven't seen enough benchmark data to convince me that stored procedures are all that fast. Everyone has an anecdote, but no one has side-by-side code where the algorithms are more-or-less the same.

[In the examples I've seen, the old application was a poorly designed mess; when the stored procedures were written, the application was re-architected. I think the design change had more impact than the platform change.]

Makeshift

"It's hard to keep good version control over stored procedures". A decent database development tool, such as Oracle's SQL Developer, is going to integrate with source system control. I agree on triggers -- hate 'em for anything other than auditing. – Coggins

What about constraints (NOT NULL, foreign keys...) : do you consider them as data or as business logic ? – Planometer

No processing. No state change. Not business logic. They're definitional items or constraints, not processing items. The rule is: No State Change. Anything like an assignment statement is processing. – Makeshift

The performance argument generally comes from the idea that application programmers generally aren't very good at SQL or knowing how to leverage a given database. Where as if someone is writing SP's, they should know this stuff, hence can get better performance. – Bremser

The value of an SP is to package functionality optimally. The implementation of that optimal can be in Java. Do some benchmarking. You'll find that Java can outperform PL/SQL if you package well. – Makeshift

I can only say, your organization must have incompetent dbms development skills. You've managed to turn some the potential benefits of proper dbms usage into liabilities. – Distortion

+1, I agree that SPs are much harder to manage than, say, C# code, plus they are often more difficult to understand for complex data transformations. SPs certainly have their place, but I avoid them where I can now with things like linq-to-sql and ORMs at my disposal. – Misjoinder

Are you saying "SPs are much harder for me to manage than ..."? They're probably not harder for a dba to manage. Or a developer on your team who is more comfortable with SQL than with C#. As a general statement without context, it's meaningless. – Distortion

This is such FUD - sprocs, triggers, and everything else in a DB exists for a purpose. That purpose is not business logic, but neither is it the business logic to control how data is marshalled or how state changes are represented in the data model. – Amorete

@annakata: Not FUD -- my exeperience. DBA's are overworked and can't respond well to developer needs; developers have more nuanced processing requirements that aren't handled well by one-size-fits-all stored procedures. – Makeshift

@annakata: "everything else in a DB exists for a purpose" -- sometimes the purposes is "because it's cool" or "our competitor has it". I used RDBMS before stored procedures were invented (I'm old). The purpose was unclear then and remains unclear. – Makeshift

Answer 8

Anything that affects data integrity must be put at the database level. Other things besides the user interface often put data into, update or delete data from the database including imports, mass updates to change a pricing scheme, hot fixes, etc. If you need to ensure the rules are always followed, put the logic in defaults and triggers.

This is not to say that it isn't a good idea to also have it in the user interface (why bother sending information that the database won't accept), but to ignore these things in the database is to court disaster.

Stowaway

For data integrity, we can add foreign keys. What this answer is missing, is the middle tier. Business logic should not go to the user interface, since we can have multiple user interfaces (Browser, Mobile app, CLI), and client side code can be manipulated. – Omasum

Answer 9

Database independence, which the questioner rules out as a consideration in this case, is the strongest argument for taking logic out of the database. The strongest argument for database independence is for the ability to sell software to companies with their own preference for a database backend.

Therefore, I'd consider the major argument for taking stored procedures out of the database to be a commercial one only, not a technical one. There may be technical reasons but there are also technical reasons for keeping it in there -- performance, integrity, and the ability to allow multiple applications to use the same API for example.

Whether or not to use SP's is also strongly influenced by the database that you are going to use. If you take database independence out of consideration then you're going to have very different experiences using T-SQL or using PL/SQL.

If you are using Oracle to develop an application then PL/SQL is an obvious choice as a language. It's is very tightly coupled with the data, continually improved in every relase, and any decent development tool is going to integratePL/SQL development with CVS or Subversion or somesuch.

Oracle's web-based Application Express development environment is even built 100% with PL/SQL.

Coggins

What about writing sprocs in Java? Wouldn't that mitigate the problem of DBMS dependence? – Recommit

@sleske,...it wouldn't if you're going moving to Sql Server :-) – Sondrasone

Answer 10

If you need database independence, you'll probably want to put all your business logic in the application layer since the standards available in the application tier are far more prevalent than those available to the database tier.

However, if database independence isn't the #1 factor and the skill-set of your team includes strong database skills, then putting the business logic in the database may prove to be the best solution. You can have your application folks doing application-specific things and your database folks making sure all the queries fly.

Of course, there's a big difference between being able to throw a SQL statement together and having "strong database skills" - if your team is closer to the former than the latter then put the logic in the application using one of the Hibernates of this world (or change your team!).

In my experience, in an Enterprise environment you'll have a single target database and skills in this area - in this case put everything you can in the database. If you're in the business of selling software, the database license costs will make database independence the biggest factor and you'll be implementing everything you can in the application tier.

Hope that helps.

Coincident

Answer 11

Putting the code in the application layer will result in a DB independent application.

Sometimes it is better to use stored procedures for performance reasons.

It (as usual) depends on the application requirements.

Teletypewriter

how often will you change your databases in your company. unless you are software vendor, the argument that DB independent application is misconceived. even if you are software vendor then it also may be implied that the vendor is being lazy and not using the DB optimisations in favor of not supporting multiple databases. – Hartford

"Putting the code in the application layer will result in a DB independent application." Not necessarily. It all depends on what other quirks of the db you are counting on. You have to do more, and code to the lowest denominator, to get DB-independence and that has costs too. – Aerial

Answer 12

It is nowadays possible to submit to subversion your stored proc code and to debug this code with good tool support.

If you use stored procs that combine sql statements you can reduce the amount of data traffic between the application and the database and reduce the number of database calls and gain big performance gains.

Once we started building in C# we made the decision not to use stored procs but now we are moving more and more code to stored procs. Especially batch processing.

However don't use triggers, use stored procs or better packages. Triggers do decrease maintainability.

Peabody

The statements are too vague to me. "gain big performance gain", it depends on how badly designed the application code is. "triggers do decrease maintainability", why? – Omasum

Answer 13

The business logic should be placed in the application/middle tier as a first choice. That way it can be expressed in the form of a domain model, be placed in source control, be split or combined with related code (refactored), etc. It also gives you some database vendor independence.

Object Oriented languages are also much more expressive than stored procedures, allowing you to better and more easily describe in code what should be happening.

The only good reasons to place code in stored procedures are: if doing so produces a significant and necessary performance benefit or if the same business code needs to be executed by multiple platforms (Java, C#, PHP). Even when using multiple platforms, there are alternatives such as web-services that might be better suited to sharing functionality.

Hovis

Re database vendor independence, application languages change much faster than databases. – Necking

You state that "application/middle tier" code can "be placed in source control". Of course you should also have your SPs under source control, regardless of how they're written. – Hannan

Answer 14

The answer in my experience lies somewhere on a spectrum of values usually determined by where your organization's skills lie.

The DBMS is a very powerful beast, which means proper or improper treatment will bring great benefit or great danger. Sadly, in too many organizations, primary attention is paid to programming staff; dbms skills, especially query development skills (as opposed to administrative) are neglected. Which is exacerbated by the fact that the ability to evaluate dbms skills is also probably missing.

And there are few programmers who sufficiently understand what they don't understand about databases.

Hence the popularity of suboptimal concepts, such as Active Records and LINQ (to throw in some obvious bias). But they are probably the best answer for such organizations.

However, note that highly scaled organizations tend to pay a lot more attention to effective use of the datastore.

Distortion

Answer 15

There is no standalone right answer to this question. It depends on the requirements of your app, the preferences and skills of your developers, and the phase of the moon.

Necking

Answer 16

Business logic is to be put in the application tier and not in the database. The reason is that a database stored procedure is always dependen on the database product you use. This break one of the advantages of the three tier model. You cannot easily change to an other database unless you provide an extra stored procedure for this database product. on the other hand sometimes, it makes sense to put logic into a stored procedure for performance optimization.

What I want to say is business logic is to be put into the application tier, but there are exceptions (mainly performance reasons)

Breeden

This answer fails the "Assume database independence is not a goal" test that the OP set. – Coggins

Answer 17

Bussiness application 'layers' are:

1. User Interface

This implements the business-user's view of h(is/er) job. It uses terms that the user is familiar with.

2. Processing

This is where calculations and data manipulation happen. Any business logic that involves changing data are implemented here.

3. Database

This could be: a normalized sequential database (the standard SQL-based DBMS's); an OO-database, storing objects wrapping the business-data; etc.

What goes Where

In getting to the above layers you need to do the necessary analysis and design. This would indicate where business logic would best be implemented: data-integrity rules and concurrency/real-time issues regarding data-updates would normally be implemented as close to the data as possible, same as would calculated fields, and this is a good pointer to stored-procedures/triggers, where data-integrity and transaction-control is absolutely necessary.

The business-rules involving the meaning and use of the data would for the most part be implemented in the Processing layer, but would also appear in the User-Interface as the user's work-flow - linking the various process in some sequence that reflects the user's job.

Trunkfish

Answer 18

Imho. there are two conflicting concerns with deciding where business logic goes in a relational database-driven app:

• maintainability
• reliability

Re. maintainability:  To allow for efficient future development, business logic belongs in the part of your application that's easiest to debug and version control.

Re. reliability:  When there's significant risk of inconsistency, business logic belongs in the database layer.  Relational databases can be designed to check for constraints on data, e.g. not allowing NULL values in specific columns, etc.  When a scenario arises in your application design where some data needs to be in a specific state which is too complex to express with these simple constraints, it can make sense to use a trigger or something similar in the database layer.

Triggers are a pain to keep up to date, especially when your app is supposed to run on client systems you don't even have access too.  But that doesn't mean it's impossible to keep track of them or update them.  S.Lott's arguments in his answer that it's a pain and a hassle are completely valid, I'll second that and have been there too.  But if you keep those limitations in mind when you first design your data layer and refrain from using triggers and functions for anything but the absolute necessities it's manageable.

In our application, most business logic is contained in the application's model layer, e.g. an invoice knows how to initialize itself from a given sales order.  When a bunch of different things are modified sequentially for a complex set of changes like this, we roll them up in a transaction to maintain consistency, instead of opting for a stored procedure.  Calculation of totals etc. are all done with methods in the model layer.  But when we need to denormalize something for performance or insert data into a 'changes' table used by all clients to figure out which objects they need to expire in their session cache, we use triggers/functions in the database layer to insert a new row and send out a notification (Postgres listen/notify stuff) from this trigger.

After having our app in the field for about a year, used by hundreds of customers every day, the only thing I would change if we were to start from scratch would be to design our system for creating database functions (or stored procedures, however you want to call them) with versioning and updates to them in mind from the get-go.

Thankfully, we do have some system in place to keep track of schema versions, so we built something on top of that to take care of replacing database functions.  It would've saved us some time now if we'd considered the need to replace them from the beginning though.

---

Of course, everything changes when you step outside of the realm of RDBMS's into tuple-storage systems like Amazon SimpleDB and Google's BigTable.  But that's a different story :)

Raglan

Answer 19

Scalability is also very important factor for pusing business logic in middle or app layer than to database layer.It should be understood that DatabaseLayer is only for interacting with Database not manipulating which is returned to or from database.

Monarchal

I agree scalability is an important factor in deciding where logic should go, but this also counsels that some logic, namely that which can be optimized better as a block within the database, probably should go there. Mike Stonebraker's example of "Get me pictures of sunsets" is a good one. If you can add a procedure in Java to determine if an image is an image of a sunset, then you can index pictures on whether they are of sunsets and you don't have to transfer every picture to the client to be analyzed. – Aerial

Answer 20

It's a continuum. IMHO the biggest factor is speed. How can u get this sucker up and running as quickly as possible while still adhering to good tenants of programming such as maintainability, performance, scalability, security, reliability etc. Often times SQL is the most concise way to express something and also happens to be the most performant many times, except for string operations etc, but that's where your CLR Procs can help. My belief is to liberally sprinkle business logic around whereever you feel it is best for the undertaking at hand. If you have a bunch of application developers who shit their pants when looking at SQL then let them use their app logic. If you really want to create a high performance application with large datasets, put as much logic in the DB as you can. Fire your DBA's and give developers ultimate freedom over their Dev databases. There is no one answer or best tool for the job. You have multiple tools so become expert at all levels of the application and you'll soon find that you're spending a lot more time writing nice consise expressive SQL where warranted and using the application layer other times. To me, ultimately, reducing the number of lines of code is what leads to simplicity. We have just converted a sql rich application with a mere 2500 lines of app code and 1000 lines of SQL to a domain model which now has 15500 lines of app code and 2500 lines of SQL to achieve what the former sql rich app did. If you can justify a 6 fold increase in code as "simplified" then go right ahead.

Vial

Agreed , a lot of sql code is buggy and time consuming and should only be reserved for when you have tested something and its slow .. Fastest way to code is not to touch sql , just serialize everything to an xml dataset and copy it as a backup – Hod

Answer 21

This is a great question! I found this after I had already asked a simliar question, but this is more specific. It came up as a result of a design change decision that I wasn't involved in making.

Basically, what I was told was that If you have millions of rows of data in your database tables, then look at putting business logic into stored procedures and triggers. That is what we are doing right now, converting a java app into stored procedures for maintainability as the java code had become convoluted.

I found this article on: The Business Logic Wars The author also made the million rows in a table argument, which I found interesting. He also added business logic in javascript, which is client side and outside of the business logic tier. I hadn't thought about this before even though I've used javascript for validation for years, to along with server side validation.

My opinion is that you want the business logic in the application/middle tier as a rule of thumb, but don't discount cases where it makes sense to put it into the database.

One last point, there is another group where I'm working presently that is doing massive database work for research and the amount of data they are dealing with is immense. Still, for them they don't have any business logic in the database itself, but keep it in the application/middle tier. For their design, the application/middle tier was the correct place for it, so I wouldn't use the size of tables as the only design consideration.

Ballinger

Answer 22

We put a lot of business logic in stored procedures - it's not ideal, but quite often it's a good balance between performance and reliability.

And we know where it is without having to search through acres of solutions and codebase!

Adroit

Answer 23

I remember reading an article somewhere that pointed out that pretty well everything can be, at some level, part of the business logic, and so the question is meaningless.

I think the example given was the display of an invoice onscreen. The decision to mark an overdue one in red is a business decision...

Rectum

I don't agree. The decision that the invoice is overdue is a business decision (can actually be quite complex for some businesses). The display color is a UI decision. – Recommit

Answer 24

Business logic is usually embodied by objects, and the various language constructs of encapsulation, inheritance, and and polymorphism. For example, if a banking application is passing around money, there may be a Money type that defines the business elements of what "money" is. This, opposed to using a primitive decimal to represent money. For this reason, well-designed OOP is where the "business logic" lives—not strictly in any layer.

Seaway