Camel Monkey

There’s a debate in most of the places I’ve seen between where to place a certain piece of logic.  When that logic interacts with data, some say it should be put in the database, preferably in a stored procedure.  Others believe it belongs with code, where the business logic should live.  The answer is really preference (since both ways will get the job done) but I believe the choice should be thought through to weigh what the repercussions might be choosing one over the other. I would like to share the guidelines I follow.

I believe the database and everything in it should deal with insertion, retrieval, and deletion.  Joins and groups are included in this.  The database was made for this and is usually  the most efficient at these kinds of tasks.
Any sort of data massaging or manipulation should reside in code.  I put any sort of manipulation of data under the category of business logic, and for me, business logic *must* reside in code.

Take, for instance, the quantity of a security you’re holding.  In your database let’s say you have a positive number in that field.  For certain reports, you need to show that number as a negative.  The data that is retrieved should be pure data, as it is in the database, and let the code do any translation it needs to.

Following this rule throughout makes the database logic (script/stored procedure) easily reusable because whoever employs it (or any other stored procedure) knows what to expect.  No guesses have to be made if the data has been pre-prepped to show a negative value or not.

You hold the logic to manipulate the data in your code, so if one of the reports now needs to show this number as a positive, you can change it in the code where it needs to and not have to worry about that change affecting other consumers.

Code offers itself to very easy version controlling, with annotation and history.  Testing can be done to prove the results, and those tests can become part of your testing suite.  This can’t be done (easily, if at all) if the logic is on the database.

Often, the claim is made that keeping this kind of logic in code is slower than keeping it in the database.  I argue that the slowdown, if any, is neglible.  However, the gain is tremendous since now you’ve made your database scripts more predictable, and made the manipulation very explicit.  If any change is required, the time saved by not having changes affect other consumers and the ability to test easily offsets the processing time saved by doing the procedure in the database.