Choose Your Poison: Does business logic belong in the database?

There are debates about where to locate the business logic in a software application. Some say that the only place is in some middle language, others argue that the database is the logical home. Once upon a time I guess that no-one gave it much thought, you build a piece of software and it ran on the server, anyone who wanted to use it logged into the server. Or you sent the software off to a user to install on their computer.

Then came two tier applications with part of the software installed on a PC and part residing on a server somewhere. Where did the business logic go? Probably with the piece on the PC, causing heartache whenever it had to be changed. So, the next intelligent move was to centralise the business logic on the server – what was on the server? Probably just the database.

N-tiered applications bought layers separating the storage of data, access to the data, business domain, presentation logic and user interface. Why? Because it means you can change one of the components without having to affect all the other pieces of the software puzzle.

So, there are choices as to where you try to place most of the business logic. In practical terms this equates to a choice between your database and some other language that extracts data from the database.

Some reasons to choose the middle language would be:

  • Your software is meant to work with as many databases as possible.
  • You have no idea about how to use database features like stored procedures.
  • You believe that implementing the business logic in a middle language provides a better separation of concerns or a looser coupling.
  • You believe that the database is only there for data persistence.
  • You believe that the middle language is a better technical choice for manipulation of data. That is, Data Access Objects and possibly the database schema are generated, Object-Relational Mapping (ORM) frameworks can be fully used.
  • The database is only one of many data sources for your software.
  • It is important to create a solution as quickly as possible at the possible expense of later maintainability.
  • There is a higher probability that your database of initial choice will change to something else.

Some reasons to choose the database would be:

  • Your software is meant to work with as many middle languages/interfaces as possible.
  • You believe in having the business logic as close to the data as possible.
  • You believe in making use of database features beyond simple data storage (particularly if you paid a lot of money for it).
  • You believe that the database is a better technical choice for manipulation of data. That is, stored procedures are used to abstract the implementation of data storage, complex queries can be used and are tuned by database experts, unit testing of database code is performed.
  • There is a higher probability that your middle language of initial choice will change to something else.

I believe that the object-relational impedance mismatch should be acknowledged and managed at the DAO interface regardless of use of database features – rather than subverting either the object model or the relational model. Except when the solution indicates otherwise.

My opinion is that the object model and the relational model should both be first-class citizens in your software. Having a stored procedure layer in the database supports the loose coupling of the models. Recognising the importance of both aspects of your software supports:

  • better use the native capabilities of your tool choices
  • better response to change.

There are some passionate views about this topic, a few starters to follow up are:

Object/Relational Mapping is the Vietnam of Computer Science. It represents a quagmire which starts well, gets more complicated as time passes, and before too long entraps its users in a commitment that has no clear demarcation point, no clear win conditions, and no clear exit strategy.

Ted Neward: The Vietnam of Computer Science

For modern databases and real world usage scenarios, I believe a Stored Procedure architecture has serious downsides and little practical benefit. Stored Procedures should be considered database assembly language: for use in only the most performance critical situations.

Coding Horror: Who Needs Stored Procedures, Anyways?

Martin Fowler: Domain Logic and SQL

Facebooktwittergoogle_plusredditpinterestlinkedinmailby feather

Leave a Reply

Your email address will not be published. Required fields are marked *