Business logic in the database

published: Wed, 2-Mar-2005   |   updated: Mon, 16-May-2005

In my view of multi-tier architectures, the business layer resides in a separate layer from the data storage layer (a.k.a. the database layer), at least when thinking about logical layers rather than physical tiers. In fact, all that appears in the data storage layer are stored procedures to Create, Retrieve, Update, and Delete records (the CRUD procedures). This simple schema has worked pretty well for me in the past.

What I've seen that hasn't worked is business logic that's forced into the database through inordinately complex stored procedures. It's never tested properly (writing unit tests for stored procedures is pretty long-winded). Business logic is too important not to test.

So there I am today presenting a slidedeck on layered architectures when we got into a discussion about business logic: where should it appear?

My thrust was that it should all appear in the business layer but that, for quicker feedback, validation logic could be duplicated in the presentation layer. After all, it would be pretty silly for the user to try and enter a blank value for some field, have it go all the way down into the business layer, be rejected, and an error code bubbling its way up through the layers to the user's screen. Far batter would be a simple validator on the window/page that flagged the error immediately. This saves on possible network traffic, gives a faster response to the user (the interface is more responsive), etc.

But, on the other hand, it would be dangerous for the validation only to occur in the presentation layer. If the user interface changes (say a Windows app is being written as well as the current web app), the developer would have to be aware that important validation only occurred in the old presentation layer and copy (cut/paste?) it over to the new presentation device.

Also -- very important point -- one of the primary reasons for having a business layer (apart from the usual "separation of duties" type argument) is that it is easily testable with unit tests that can be automated. Presentation layers are notoriously difficult to test (read: bloody hard to do therefore it tends to get skipped when the rush is on).

So, I made the argument that duplication of validation logic is if not good then at least acceptable.

Similarly you can make an argument that some validation logic get duplicated in the data storage layer. Here it's to avoid the problem of getting invalid data into the database from the user using Query Analyzer or something similar to push some data "through the back door".

We'd batted this back and forth a little while (like me, people were a little dubious about the duplication of not necessarily code but functionality), when Greg B. came up with a business logic scenario that is extremely hard to do anywhere but in the database.

Suppose you have a business rule which states that, although there may be several objects of type X, their IDs (or names or codes or...) must be unique. In a multi-user environment, the only possible way this rule can be enforced is by a key constraint in the database.

In other words, my simple world view of CRUD procedures only in the database is not quite deep enough. Constraints in the database play a part too.

Now, in my defense, I did know about constraints, but I just hadn't formulated the position that constraints are business rules, and furthermore a type of rule that can only be enforced in the database. Also, because constraints are part of the system's business logic, they should have unit tests written for them.

Update

Dan Miser sent me this link that explains a strategy for testing stored procedures.

John Waters, a Falafel consultant I worked with before, admonished me forgetting about some SQL unit tests that he'd written for an application I'd written. Actually, he wrote unit tests for the business layer, which by default worked on "real" data in a SQL Server instance, so that doesn't really count. Sorry, John <g>.