Moving Logic Into the Database

As my programming skills have improved over the years, I find myself demanding more precise and guaranteed control over my data. Simply inserting into the database and hoping for the best is not good enough anymore. Using transactions and foreign keys are good first steps in ensuring data integrity, but with some of the more advanced databases, you can go further still and move programming logic into the database itself, stripping it from your PHP code.

I recently picked up development of GForge, an open source software development/project management tool. Part of what I wanted to do was create a first-class "Project Manager", similar to MS Project or similar tools which do Gantt charting, and I wanted to do it with PHP using a web interface.

In Gantt charting, you have a series of tasks, which can be constrained by prior tasks, as in the example below. Task B is dependent on the completion of Task A, and Task C is dependent on Task B.

click for larger image

That sounds easy enough, but what if a user attempts to enter a start date on Task B that is earlier than the end date of Task A? At the time you insert Task B, or update its entry in the database, you could have PHP logic that queries the database and checks the end date of task A. That's a couple lines of PHP. But what if you need to delay Task A by a week? Now you need a recursive function in PHP which will go all the way down the line and delay Task B and then Task C by the proper amount and commit all the changes to the db. Suddenly, you are looking at a lot of queries and recursion in PHP and you've got a performance problem, especially if you have a high-traffic web application.