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 ?
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:
INSERT, UPDATE, DELETE and SELECT).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.
SELECTs directly from tables (the data tier), the model has been broken.
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.
Atwood's advice from 2004 rings true still today, only we now have the benefit of ORM as well.
http://blog.codinghorror.com/who-needs-stored-procedures-anyways/
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.
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:
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:
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.
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:
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.
