Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

SQL etc

Posted Jan 13, 2003

Moving Logic Into the Database

By DatabaseJournal.com Staff

by Tim Perdue

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.

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.

SQL etc Archives

Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM