Some colleagues of mine have told me that having business logic in stored procedures in the database violates the three-tier separation architecture, since the database belongs to the data layer whereas stored procedures are business logic.

I think the world would be a very grim place without stored procedures.

Do they really violate the three-tier separation ?

  • 9
    Just ask them have they not heard of the 3 1/2 tier architecture...
    – dreza
    Commented Oct 21, 2012 at 21:28
  • 7
    Remember that tiers and layers are not one and the same.
    – NoChance
    Commented Oct 22, 2012 at 4:37
  • 2
    @emmad-kareem This question helped me ( stackoverflow.com/questions/120438/… ). The problem is that spanish (my mother tongue) language technical literature uses a single word for it ("capa"), whereas english has two very distinct words. Commented Oct 22, 2012 at 12:38
  • 1
    @user1598390, you are correct it could get confusing specially that the world of software has not got enough rigor when it comes to definitions in one language let alone across languages.
    – NoChance
    Commented Oct 22, 2012 at 16:32
  • 1
    3-Tier architecture is a logical concept, not a physical concept. You can implement business rules using stored procedures, and while physically in the database, those stored procedures are still part of the business logic tier. Commented Nov 26, 2015 at 18:42

6 Answers 6


Your colleagues are conflating architecture with implementation.

The idea behind a multi-tiered application is simply that it's broken up into parts that encapsulate certain kinds of processing (storage, business logic, presentation) and communicate with each other using well-defined interfaces. Just as it's possible to successfully do things that resemble object-oriented programming in non-object-oriented languages, it's possible to do the same with multiple tiers within one environment, such as a database server. What doing either of those successfully have in common is a need for care, discipline and an understanding of the compromises involved.

Let's look at a three-tiered application where two of the tiers have been implemented on a database:

  • Data Tier: Consists of database tables accessed using the four standard table operations (INSERT, UPDATE, DELETE and SELECT).
  • Logic Tier: Consists of stored procedures that implement only business logic and access the data tier using only the methods outlined above.
  • Presentation Tier: Consists of a web server running code that accesses the logic tier by making only stored procedure calls.

This is a perfectly-acceptable model, but it comes with some tradeoffs. The business logic is implemented in a way that gives it fast, easy access to the data tier and may allow doing things that would have to be done "the hard way" by a logic tier outside the database. What you give up are the ability to easily move either tier to some other bit of technology and carefree implementation (i.e., you have to be extra careful that the tiers don't use facilities that are available in the database but outside their defined interfaces).

Whether or not this kind of thing and the tradeoffs it brings are acceptable in a given situation is something you and your colleagues have to determine using your judgment.

  • 2
    So I can them them that stored procedures are part of the logic tier, architecture-wise, regardless the fact that they are stored in the database ? Commented Oct 22, 2012 at 3:06
  • 3
    @user1598390: yes. Although it would be layer to say 3-layer, and not 3 tier.
    – jmoreno
    Commented Oct 22, 2012 at 3:33
  • 5
    @user1598390: You can say that as long as you can prove it. The first time the presentation layer SELECTs directly from tables (the data tier), the model has been broken.
    – Blrfl
    Commented Oct 22, 2012 at 10:48
  • @blrfl That's something I've taken care of ;) Commented Oct 22, 2012 at 12:40
  • 2
    @user1598390: that's fine, just remember that the goal is logical seperation of concerns, not putting things on different hardware.
    – jmoreno
    Commented Oct 22, 2012 at 14:46

Stored procedures are powerful enough to let you code a violation of three-tier separation by bringing business logic into the RDBMS layer. However, this is your decision, not an inherent flaw of stored procedures. You can limit your SPs to servicing the needs of your data layer, while keeping your application logic in the application layer of your architecture.

There is a rare but important exception to the rule of separation, when you need stored procedures (specifically, a group of triggers) to contain business logic. This happens when your application needs to produce a lot of on-the-fly data aggregations that touch millions of rows. In cases like that, triggers can be set up to maintain pre-aggregated data for use of the business layer. This should be done only in situations when without pre-aggregation your application would be unacceptably slow.

  • 7
    +1 for mentioning that occasionally you want some logic to live in the database for performance reasons because an RDBMS will generally do set operations orders of magnitude faster than your application code. Though obviously this is only when performance is critical and can't be met in app code, the vast majority of modern database backed apps are CRUD apps and have zero use for such benefits. Commented Oct 22, 2012 at 4:26
  • 1
    amen. People seem to think that sprocs = business "code". They should be thought of as a DB 'API', and then they make a lot more sense. Of course, they can also fix the edge cases where you need performance from your logic too!
    – gbjbaanb
    Commented Jul 5, 2013 at 9:52
  • and why do we assume that a DB would be more performant than some compiled code in one or multiple nodes? I disagree with that statement. If my Java or C# code is running on a huge performant server, would you still think you´d be more performant with a SQL Server Express database? You can place business logic in database, but don´t expect it to be as testeable and mantainable as if it was where it should, in the application.
    – diegosasw
    Commented Jan 2, 2020 at 12:02
  • @iberodev I am not sure I understand with which of my statement do you disagree. Commented Jan 2, 2020 at 13:09
  • 1
    @dasblinkenlight sorry, I meant to quote Jimmy's first comment when he mentions that RDBMS updates the state of the application faster than application code. It depends on many things and the chosen architecture. In fact I have an application where business logic is in the app and I use in-memory persistence. I doubt moving that logic to a RDBMS could get any faster. I agree with your answer and with your explanation on why SP are not a violation of the 3tier separation per se unless they contain business logic, in which case the violation is already there.
    – diegosasw
    Commented Jan 2, 2020 at 15:46

Atwood's advice from 2004 rings true still today, only we now have the benefit of ORM as well.


Stored Procedures should be considered database assembly language: for use in only the most performance critical situations. There are plenty of ways to design a solid, high performing data access layer without resorting to Stored Procedures; you'll realize a lot of benefits if you stick with parameterized SQL and a single coherent development environment.

  • 1
    In my 20 years experience in a big company, stored procedures are not used for returning rows (views are used for that), nor are they used for every simple inserts or updates (inline sql is used for that). They are used mostly for long operations that require no user interaction, requiring iterating large sets of data for summarizing and doing inserts or updates based on some business logic, on a row basis, like end-of-moth closings or dayly batch processing of transactions. The author of the article seems to have being using stored procedures to return rows and that's why he heates them. Commented Dec 4, 2014 at 16:25

Brief Summary: It really depends on your usage of stored procedures and business requirements.

There are a number of projects that do use a three-tier architecture and depending on the nature of business requirements there might be need to shift some operations to a data tier.

Speaking about terminology, in general words these tiers described as:

  • The presentation tier, or user services layer - gives a user access to the application.
  • The middle tier, or business services layer - consists of business and data rules.
  • The data tier, or data services layer - interacts with persistent data usually stored in a database or in permanent storage.

Usually for the given architecture, the middle tier or business services layer, consists of business and data rules. However, sometimes it makes big difference to shift heavy set base operations and/or data rules to be done in data tier - through set of stored procedures.

The benefits of three-tier designs are:

During an application's life cycle, the three-tier approach provides benefits such as reusability, flexibility, manageability, maintainability, and scalability. You can share and reuse the components and services you create, and you can distribute them across a network of computers as needed. You can divide large and complex projects into simpler projects and assign them to different programmers or programming teams. You can also deploy components and services on a server to help keep up with changes, and you can redeploy them as growth of the application's user base, data, and transaction volume increases.

Thus, it is really a case-base approach which has trade-offs in itself. However, Microsoft design guidelines of Three-Tier Architecture Model recommends to keep your business logic in middle-tier.


Tier actually means different machine, layer means different logical separation. With stored procedures you have the data layer and (at least part of) the business logic layer in the same tier. Putting the business logic in the stored procedures violates the 3-tired architecture but is questionable whether it violates a 3-layered architecture; one sure thing is that it sure isn't a good example of separation of concerns.

A layer is a logical structuring mechanism for the elements that make up your software solution; a tier is a physical structuring mechanism for the system infrastructure. (Reference)

In my opinion there are two major problems with building business logic in the database:

  1. Code and libraries: you will find fewer programmers being able to program in SQL, PL/SQL, TSQL etc. than in C#, Java etc. Programming languages also have the advantage of great IDEs, great libraries and frameworks.

  2. Horizontal scalability: the only way you can scale your system is by changing the physical server on which the database with a more powerful one, which is rather expensive (a server with 64 GB RAM); relational databases scale horizontally very bad, and even at greater expenses. While, with business logic in OO-built server you can scale horizontally pretty well by putting the server on many nodes (in Java many applications servers support this).


We had this debate in our office some times back, I was favouring Database Development, I have the following view on it:

  1. If you are using Oracle Database you should utilise PL/SQL as much as possible, Because for sure companies who invest will stick to Oracle for at least even 10 years from now. While in the applications yesterday you were using Oracle Forms, today .NET Web forms, then MVC, then tomorrow you will use AngularJS and just need RESTful APIs. If you maximum logic is in the database you can easily migrate to the new front end technologies.
  2. Database development is very fast and very efficient performance wise. Just to give you an example, in our project there were 7 application developers and one database developer, and 80% of the logic was in the database.
  3. If you are using Oracle you can use utilities to directly convert your database procedures into a RESTful API.

The strongest argument the application developers give is that the business logic should be independent of the database so you can easily change the database. I think if a company is using Oracle, why they will switch to another technology? Instead the chances of getting application logic obsolete are higher. The issue is mostly new talent of the database resource is lacking, most people start will simple websites where they are using MySQL or SQL Server. These people then become senior leads and have emotional attachment with the application layer :) they even don't want to understand or debate.

  • 1
    "If you are using Oracle Database you should utilised PL/SQL as much as possible," Stored procedures add load to what is typically the most bottle-necked and hard to scale system in an architecture. They are also a pain to manage from a version control and unit-testing perspective "Because for sure companies who invest will stick to oracle for at least even 10 years from now." This is just nonsense. What makes you think that? If you fill your system with stupid PL/SQL procedural garbage, you might keep a company from moving to something contemporary. That could be true.
    – JimmyJames
    Commented Jan 31, 2018 at 15:28

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