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).