Moving Business Logic to the Database

Firstly, if you're likely to be changing db vendors (data sets are getting large and you're considering Oracle, or licensing costs are an issue so you're considering moving from MSSQL to MySQL, for example), keep the db dumb. However, on the other hand if you are considering changing scripting language (or using multiple), having more logic in the db might allow you to make such changes more easily.

Also, if you work in a larger shop where you don't have permission to deploy stored procedures and UDFs without review by a DBA, that might make coding in the db impractical (depending on how responsive the DBAs are).

If you're looking for optimal performance, putting the code in the db may make sense, but for most applications, trading off maintainability for performance is almost always a bad choice, so unless you know you're going to have a performance problem, don't move code to the db just to try to speed up your app. It just isn't worth it.

In general I like to keep my code in a 3GL like ColdFusion. It means I can write OO code which gives me lots of tools for writing maintainable code. I get decent code completion and it's easy to set up unit testing. It's easy to put the code into subversion, and I have a wide range of encapsulation and reuse mechanisms for keeping my code readable and DRY.

Of course, you can get some of that in T-SQL (or your dialect of choice). There is no reason why you can't write UDFs, I'm hoping IDE support for SQL is improving, you can (and should) put all of your SQL scripts into source control and automate the running of them as part of your build process. I still don't feel like putting substantial code into SQL is as easy or maintainable as keeping it in (say) ColdFusion, but I'd love to hear below if anyone has managed to create a setup that allows them to write equally maintainable code in SQL as in CF.

In my use case (being able to generate thousands of web applications quickly and cost effectively), I tend to value consistency and development efficiency over optimization of other criteria. If I only built five or ten projects a year, I might decide on a case by case basis what to implement in SQL and what to put in ColdFusion (and I might move away from a 1:1:1 relationship between business objects, service classes and DAOs). In my use case there just isn't a justifiable business ROI on the effort required to think about the trade offs every time. I use a heuristic that I'll put all code in the 3GL and for the kind of projects I build it works quite well. Why didn't I decide to move things like getter and setter logic to the db? Here's why . . .

At first it seems like a no brainer to move some code into the db. Want User.FullName? Just create a view with a field that concatenates first name and last name and you're done. Returning User.Age based on the tbl_User.DateofBirth column is a little more work - but not much.

However, what happens for a getPrice() method for a product where the price depends on the total orders by that user over the last rolling 12 month period and those of all of the other users within their buying group - requiring complex joins between multiple order item and order user, user group and various discount group tables? From a performance perspective you could argue that it will probably be more performant in SQL, but if there is a lot of conditional logic in addition to the set based operations, you might want to be able to break down the code into component parts and while UDFs and SP's calling SPs can be used, I'd rather keep such complex business logic in my 3GL programming/scripting languages.

However, some would look at that and think it was a perfect example of the benefits of putting business logic within SQL as it will be more performant and available independent of the programming language you choose to use. The final nail in the coffin (for me) was when I asked, what if the smart getter needs to access third party web services? For example, I want to be able to getShippingPrice() for an order and that is going to require either finding a way to write a SP that can call a web service (which even if possible is something most supporters of SQL would probably agree isn't exactly what it was designed for).

I decided for my use case to go for a consistency of having all of my business logic (including smart getters and setters and data validation) within my business objects. If performance was a concern or consistency wasn't such a big thing for my specific use case I can understand why someone would decide to put some of that logic in SQL and why it could be a valid choice, but I think it's important to think through all of the implications.

What do you think? Do you put business logic in the db? If so what (and if not, why not?). What are the rules of thumb you use for deciding what goes in the db?