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 ?
Best Answer
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,DELETEandSELECT).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.