Rails creator David Heinemeier Hansson argues that you should keep your business logic in your business layer rather than in your database. In other words, leave those stored procedures, triggers, and constraints aside. This is a debate that I’ve had many times on various projects. I’ve walked into a room more than once and had to face down a customer who wanted to restrict all access to the database to an API composed of stored procedures that my code could call. I’ve won every time but one.
On the project where I lost, the database was massive and complex — the database calls literally took minutes to run, and the customer understood that data better than we ever would. They were hardcore PL/SQL and Powerbuilder programmers, and we were building a Web application in Perl and ColdFusion. There were a number of hoops we had to jump through but things turned out pretty well in the end, and that was definitely the right approach for the project. There was no need to duplicate expertise between their IT staff and the group I was working with, and we got the project done in only four times the amount of time originally allotted. Success!
Every other time, when the database was much simpler (and indeed nearly always designed to the specifications provided by the development team), I’ve been able to avoid shoving logic into the database. I agree with Hansson’s core argument for it, which is that there’s just no reason for it. Your business logic should all live together. I would perhaps make an exception if multiple applications running on different platforms needed to access the database and duplicating the logic wouldn’t make sense, but these days I think a better approach would be to stick a Web service in the middle and use that as the common ground among applications.
One area where he and I might disagree is on the use of constriants. I’m not a fan of stored procedures or triggers, but constraints strike me as a necessary part of a decent database. If you create a child object that must be associated with a specific parent object, including a foreign key relationship costs you nothing and guarantees that your data integrity will not be compromised by someone’s bad code. The constraint still has to be enforced in the business layer anyway, but including it in the database both insures you against mistakes and makes the database layer more clearly documented. If someone comes along later and dumps the database schema they’ll see what the designer intended (without being forced to look at the application source as well).
October 3, 2005 at 10:14 pm
Meaning, constraints are to your data what assertions and tests are to your code.
October 4, 2005 at 12:17 am
There is some business logic that one would want to keep in the database layer, just to maintain the integrity of the database and keep silly programmers from accidentally messing it up.
I’m talking about thing like NOT NULL and like constraints. That’s exactly where triggers and stored procs come in handy, especially for saving the data from a missed exception in the middle tier.
“Oh, just design it in the app layer” is a novice response — and obviously from someone who hasn’t been bitten by sloppy code (or someone who doesn’t understand ACID and what it means when the DB doesn’t care about one’s data). It is better to reject bad data, rather than silently accepting and changing it (truncating, accepting invalid data, etc).
Yes, just as in anything, one can go overboard and design an supply-chain system all in stored procs, but that is no reason to eschew stored procs altogether.
October 4, 2005 at 5:59 am
My experience agrees with Bryan’s – it is easy to go overboard, but whenever possible, it is best to have the database itself help maintain its integrity thru triggers, stored procs, and basic constraints. This is especially true if you have multiple apps re-using the same data store.
Keith put it very succinctly 🙂
October 4, 2005 at 9:12 am
I think that there’s a line to be drawn between constraints and triggers/stored procs. I’m in favor of using “primary key”, “not null”, and foreign keys to insure the integrity of your data. Just as I’m in favor of using transactions. The database is your last line of defense against user and programmer error, and dealing with a database with bad data is the worst nightmare there is. I don’t qualify that stuff as business logic, though.
What I’m talking about is whether you have a method in your application called “processOrder()” or a stored procedure called “processOrder”. If it’s up to me, I’ll put that at the application layer rather than the database layer.
October 4, 2005 at 2:39 pm
I mostly agree with you Rafe, but I think you made the point yourself that it’s something that has to be determined case-by-case based on scale and purpose.
At my job, I maintain a couple of databases that are used by dozens of applications written by several different groups of developers for all kinds of disaparate purposes. I can’t trust them with access to the raw tables, but I can’t write their application code for them, either.
In that situation a data access layer on top of the database layer is vital. Of course, as you say, it doesn’t have to be stored procedures and views. It could be a web service or some other middle tier, but providing it in the database means they don’t have to come up with new ways of making data API calls and I can keep everything in one place.
October 5, 2005 at 12:28 am
I think ultimately the systems that survive are going to be the ones that segregate the transaction systems from the reporting systems. That means that the business logic will be primarily in a layer abstracted from the database but in the context of a data path that is understood from a high level.
From such a perspective there will be ‘code blocks’ that are practically self-explanatory in the transaction code.
I have been working with multidimensional databases and DW for over a decade and rarely have I seen any systems that don’t have to be extensively reorganized to make sense from the perspective of an analytical consumer. Almost inevitably an operational datastore is required to make sense of transactions that don’t add up.
The ones that work are those where the ERs are clean, naming conventions are followed, and the functions of stored procedures can be described in a few short sentences. In other words, discipline your RDBMS – let it express as much business logic as possible through structure.
When it comes to reporting systems, the tools to express analytical business functions are very advanced, and people with experience in working through those tools can whip transactional data into shape readily – so long as somebody understands what the business rules are.
October 5, 2005 at 10:55 am
As it relates to the use of dynamic SQL (an application that writes SQL code and/or HTML files based on user input or query results from the database, is it of any use to store the logic as a record in a separate repository of the database and making it accessible to the application via a query?
Insert logical_block_result_string as [logical_block_B] into tbl_logic
Select logical_block_A | logical_block_B as [logical_block] from tbl_logic.
exec(logical_block)
October 7, 2005 at 9:58 pm
Database Meaning
Rafe Colburn pointed to a blog posting by David Heinemeier Hansson where he that you should keep your business logic in your business layer rather than in your database which then lead to a blog post by Martin Fowler entitled…
November 8, 2005 at 5:45 pm
This is the first entry