Geeks With Blogs

News Locations of visitors to this page

Graeme Reisinger Welcome to my Office. My Other Office.

Some time ago, I found myself in the difficult situation of trying to explain why business logic shouldn't be placed in the database, but instead in the code (preferably in a Business Logic layer or Object Model layer).

The situation seemed so clear to me.  It was striking how differently others in the team saw it. 

You may find yourself in a situation similar to this one, where you are working on a database that contains *many* business rules.  Perhaps it started out as a set of tables that very closely mirrored the Graphical User Interface presented to your users.  It may have additionally contained an initial set of rules that dictated whether additional rows in other tables were to be updated.

Now, throw in to the mix the fact that the customer realized the application almost meets their needs, but not quite.  Because of one small change in the business rules governing workflow in their ofice, the existing design your team had put into place now has to pretty much go out the window.  (Well, maybe just migrated to a better model and then later abandoned).

You may find yourself thinking back to the original design, and how much more flexible it would be now to change, if only the database were used solely for the porpose of efficient storing and retrieval of data.  Additionally, you may have further complications to unravel due to the fact that the database more resembles the objects in the Graphical User Interface, rather than a relational model that is unknowing and uncaring about what ever GUI object may display the data.

I did some searching around on the internet, and apparently this is a very common problem.  One article in particular by Chad Z. Hower caught my eye:

Pay particular attention to his definition of 'Business Logic'.  If I were to try to capture his definition into one sentence, I would describe business logic as "any decision, formatting, additional work, or decision-making that can be encumbered upon a data transaction, outside of the normal constraints that exist with in a database".

In his article, Chad outlines a detailed explanation of pros and cons associated with this very decision.  Surprisingly, in his post I actually found identically worded explanations (compared to those I have heard before) as to why placing business logic into the database is a good thing.  I must clarify: he DOES NOT espouse this practice (and neither do I!), but I'm saying I've heard the same explanations before, verbatim.

This perspective (in favor of placing logic in the database) can be a very frustrating one to refute, in part because it seems so simple for people to list the obvious, more immediate benefits.  Warning: if you are reading this post right now, you have probably heard one or more of these benefits debated in discussions already at work.

You may hear that putting the logic into the database:

  • Allows your team to save time by avoiding a testing cycle (for e.g. when the team delivers a software patch or version upgrade to existing components).
  • Is "easier" than having to locate and maintain it in the code base somewhere
  • Is less buggy, because it requires less testing
  • Gives an additional layer of protection against bugs, because it does not solely rely on a bug-free code base

Pretty simple, obvious benefits, right?

The cons are much more subtle.  And in my opinion, they are much more sinister than the pros, because their consequences can be paralyzing when it comes time to make changes to the code, the GUI, the database, or a combination of these.

  • When logic is placed in the database, it is often in one (or several) additional locations outside of the database.  This is often the result of a "just in case" approach put into action by a developer, because he is unsure whether future changes might be applied to the database that can break the code segment he is currently working on.  He is thinking to himself, "Just to be sure, I better place the same logic in the code, in case the rule gets changed in the database..."
  • There may be several copies of the same logic, encapsulated in many database objects that serve data to different layers of the application.  Or perhaps several slightly different versions of the same object.  These things evolve over time - unfortunately, it's up to you to figure them out  :-/.
  • Rather than relying solely on constraints to uphold data integrity, unknown (probably countless) layers of subtle rules are being applied at run time to update the data in the database, as it is parsed.  Over time, these rules make the database fragile to the expectations placed upon the data model itself.  Meaning: "You should have known that you can only apply 'Y' for yes, 'N' for no, and 'M' for maybe."  When the paradigm shifts, the data model (and all its associated rules) become obsolete almost instantly.  What I mean by this is, the database becomes expectant to certain kinds of specific updates.  It will eventually end up breaking the business model when a new developer comes along, expecting nothing out of the ordinary when updating the database.  He didn't know that only data types of char(1) and only values of 'Y', 'N', or 'M' could be applied.
  • Which team member was it that worked on those stored procedures that tied all the logic together correctly?
  • Does that team member even still work here?

These cons build on each other.  If not realized, they can either paralyze a team that wishes to make changes to their application, or force them to abandon the old model and start anew, with a fresh application code base, and (sigh) a fresh database.

The data will still need to be migrated however.



Posted on Sunday, June 27, 2010 12:56 PM Architecture , SQL Server | Back to top

Comments on this post: Business Logic: Should it exist in the code or in the database?

No comments posted yet.
Your comment:
 (will show your gravatar)

Copyright © HighAltitudeCoder | Powered by: