A Programmer's Dream

A good reason to get data via Stored Procedures.

Posted by Stephen Wrighton on 16 Jan 2007

And that reason is, poorly designed databases.

I have a simple survey application. It's database has two tables (a models table and a results table) and the website itself has only two pages. The survey and the results page.

It worked great, until the market folks decided to change the models which are being surveyed under. When that happen, guess who was stuck having to make changes. Of course I can't just remove the existing models, because that means you wouldn't be able to see the results for those models.

So after I added in a logical deletion bit to the models table, I then had to go in and rebuild the web application to filter based on the deletion bit. It's not complicated, but it's a matter of finding the source code, making the changes, recompiling and then re-deploying. Time consuming and frustrating more than problematic. Of course if I had used Stored Procedures (or even VIEWS) to populate the various pages then I could have just made a few extra changes in the database and been done with it.

Oh well. I've probably not learned my lesson though.

Tweet me @kidananubix if you like this post.