Pros and Cons on where to place business logic: app level or DB

Question

Hi,

I always again encounter discussions about where to place the business logic: inside a business layer in the application code or down in the DB in terms of stored procedures. Personally I'd tend to the 1st approach, but I'd like to hear some opinions from your part first, without influencing you with my personal views. I know there doesn't exist a one-size-fits-all solution and it often depends on many factors, but we can discuss about that.

Btw, we are in the context of web applications (having an Oracle DB) and our current approach is to have

• UI layer which accepts UI input and does a first, client-side validation
• Business layer with a number of service-classes which contains the business logic including validation for user input (server-side)
• Data Access Layer which calls stored procedures from the DB for doing persistency/read operations

Many people however tend to move the business layer stuff (especially regarding the validation) down to the DB in terms of stored procedures.

What do you think about it? I'd like to discuss.

Answer 1

Logic in the database is a maintenance nightmare. In the occasions where it's really needed one ought to document it really well and place it in text format together with the other source code.

Answer 2

It normally sucks for various reasons. If you work object oriented, then stored procedures are not eaxactls a good place for logic - because your objects don't exist there anymore. An object may be in multiple tables.

Second. SQL is a freaking bad langauge to code complex logic in. It just is no tdone for that - one reason SQL Server allows SP's to be written in .NET. Try calculating a hash in SQL and you will understand what I mean - all kinds of string manupulations are another area. Dirty as hell.

SP's in general are pretty often done with idiotic arguments. Idiotic like the arguments the people bring to defend them are simply not true at all. Frans Bourma has a list of the most often used fallacies anda good explanation why the arguments are mostly stupid ramblings at http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx - and yes, it is this level of idiocy (like people not even reading the documentation or thinking about what they actually say, in all the consequences).

I personally have limited systmes with stored procedures, and the ones I have are: Limited complexity, but high performance. Basically no inheritance because the object model is simple, the transactional logic in the SP's is not overly complex AND I need/want extreme small locking speed, so certain operations are moved into stored procedures. On top, this particular application has a very unusual object model, too (objects aare dynamically streamed from various sources, never updated, always replaced, and all changes HAVE to go through services and not be done on the object - sometimes because a change is "asked for" on another computer in another country in anothe organization.

A good example is an accounting system which is high performance (because it is tracking trades from fully automated trading systems). The logic in every SP is not really complex, but I want to have as little SQL going back and forth as possible.

Now, bad sides of Stored procedures are also a lot tool wise. There is no proper testing framework, no mocking frameowrk, source control itnegration is kind of a little awkward (but doable with the right toolset). Integrated debugging? Well, my great thanks to Microsoft and Visual Studio - that actually works (breakpoint in stored procedure - really nice).

I have yet to see one approach using a lot of stored procedures that was not defended with totally borked arguments - on the level of actually being a demonstration of "employee should be fired" level of thinking. Maybe they are out there, but I have not seen them.

Answer 3

Having the data keep itself sane whether or not someone's using your application, another application, or a SQL tool can be valuable in itself.

Got a value that should never be NULL? - good, design the database to enforce that rule. Got a relationship between two tables that should always exist? - good, put a foreign key constraint in.

Got values that should be unique in your problem domain? - good, put a unique constraint in. Got a string that should always be between 6-10 characters? - good, add a check constraint.

These are all basic, easy to add to the database, and give you a level of confidence that your application isn't going to fall over when it tries to load something from the database that someone has mangled by hand. And to some extent they can be considered business logic. (After all, you're deriving all of these from concrete facts about your problem domain).

So to that extent, I would put that sort of business logic in the database. Yes, up in your application, you'd want to apply similar checks, to give a more pleasant user experience. But I'd rather have my application fall over (as a last resort) when it tries to put something invalid into the database than to discover this fact 6 months later.

Answer 4

I've only seen one case where logic in the Stored Procs made sense; basically it was performance related: lots of data to move and crunch. The saving grace was that the logic wasn't hugely complex - but the SP was still a nightmare. It was deemed too slow to do it in the app code.

So at a guess - that's maybe a 1 in 50+ project senario?

You're defining factors are going to be:

• Ownership: who (which system / component) owns the data and who owns the rules / logic that apply to it? If the database is "owned" by a specific component then that should contain the business logic as teh database is merely it's repository; if the database is an entity in it's own right then there might be a case for encapsulating the logic in there also. You may even have a more subtle break where certain decisions are made in one place and others elsewhere - Triggers are a possible example of that.
• Managability: logic in app code - much better testing support, etc.
• Complexity: (linked to managability) logic in app code.
• Performance: if the volumes of data are large, and using app code is too slow you might be forced to put it there.
• Consistency: all in app code - and hope you don't have any performance issues. make sure you document exceptions very well.