Software developers love re-factoring code. And why shouldn’t they. It’s quick (sometimes) and when done correctly it’ll reduce the amount of code, and speed up application response time. DBAs like re-factoring code as well. We get the same benefits when done correctly. Re-factoring the database schema on the other hand, is a frigging nightmare.
Changing around code is easy, moving 100,000,000 records from one table to another in a timely fashion isn’t. It sucks, big time.
However as databases evolve over the years things change, and the database may need to change with them.
One client that I was working with (they shale remain nameless, but they know who they are; I hope) has a database that really, REALLY needs to be re-factored. They’ve got tables where in one table the key is a single column, but another table that joins to it uses two columns concatenated together as the key. They’ve got columns which hold numeric values but have varchar data types because a long time ago there were varchar values in there. They use ISNULL to check for nulls in procedures and functions all over the place, when the application logic doesn’t allow nulls in the table (sometimes the database column has NOT NULL set for the column, but the WHERE clause still has ISNULL() functions around the column names.
I’ve pointed this out to them a couple of times now, and the response has sadly been “We don’t have the time, and there’s no enough business impact to justify the time.”. The problem with that statement, is that servers cost money, and there’s are always running at maximum capacity with no room to grow. To move into a larger server requires they move from a dual chip server to a quad chip server. That costs time to do the move, and money for the SQL License every month (they are hosted by an managed services provider). Those costs will greatly out weigh the costs of doing a full database redesign into a more normal database design.
The worst part is that they know they will have to do it eventually if they want to keep growing the business, but they won’t ever do it. They will just keep flushing more and more money down the drain throwing hardware at the problem instead of maximizing the investment that they currently have in place.
That database redesign that you’ve been avoiding, it’s time to quit avoiding it and do it. In the long run it’ll be worth it. Granted you won’t get newer hardware quite as often, but in this economy we all need to do our part and help save the company money where we can. This is one place where we the DBAs can really help out.
Denny
