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.

Some colleagues of mine have told me that having business logic in stored procedures in the database violates the three-tier separation, 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 ?

share|improve this question
5  
Just ask them have they not heard of the 3 1/2 tier architecture... –  dreza Oct 21 '12 at 21:28
5  
Remember that tiers and layers are not one and the same. –  Emmad Kareem Oct 22 '12 at 4:37
1  
@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. –  user61852 Oct 22 '12 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. –  Emmad Kareem Oct 22 '12 at 16:32
    
possible duplicate of Database Application Design Best Practices –  gnat Apr 10 '13 at 7:48

4 Answers 4

up vote 20 down vote accepted

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.

share|improve this answer
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 ? –  user61852 Oct 22 '12 at 3:06
3  
@user1598390: yes. Although it would be layer to say 3-layer, and not 3 tier. –  jmoreno Oct 22 '12 at 3:33
2  
@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 Oct 22 '12 at 10:48
    
@blrfl That's something I've taken care of ;) –  user61852 Oct 22 '12 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 Oct 22 '12 at 14:46

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.

share|improve this answer

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).

share|improve this answer

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.

share|improve this answer
3  
+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. –  Jimmy Hoffa Oct 22 '12 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 Jul 5 '13 at 9:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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