Refactoring Databases

Posted at 22:39 on 14 July 2008

I was late home this evening because I was sorting out a problem for one of our clients. One particular database table has a couple of triggers that update some tables in other databases. It turned out that someone had renamed one of these other databases but had forgotten all about the triggers, which were throwing errors as a result. Fortunately, it didn't take too long to track down the problem and fix it.

This kind of thing is very easy to do if you have a lot of database interdependencies and stored procedures like this. Databases are by nature very tightly coupled to your other applications and systems, and making major changes to them calls for nerves of steel at times. They are frequently accessed not only by the applications that you know about but often by several that you don't, especially in larger organisations. With that in mind, any smart advice and help that you can get is most definitely welcome.

This is why Refactoring Databases by Scott Ambler and Pramod J Sadalage (Addison-Wesley Signature Series) is a book that I highly recommend to any developer (or DBA) working with databases in any capacity. It has a lot of useful advice on how to handle different scenarios that you may come across (including political ones), as well as details about the precise mechanics of specific refactorings, and shows you that with a disciplined, iterative, step by step approach, backed up by a suite of unit, integration and regression tests, it needn't be all that scary.