Database Refactoring

I am in the process of reading a book on database refactoring - Refactoring Databases - Evolutionary Database Design

Before I get too deep into this book, I wanted to talk about how I currently manage refactoring in the database work that I do. This is a nuts and bolts implementation explanation rather than a generic, more globally applicable method.

First, I generally work in SQL Server, and virtually always use stored procedures when accessing a SQL Server database. I initially did so because a boss I worked for insisted upon it, however in the longer term, I discovered that by using stored procedures, I could mask changes in the database. That is, I could change internal implementations, yet keep the public interface the same so that any applications using the stored procedure could continue to work. For instance, imagine I have the following stored procedure (greatly simplified):

CREATE PROCEDURE spInsertCustomer
@CustomerName nvarchar(120),
@CustomerAddress nvarchar(120),
@CustomerPhone nvarchar(120),
@CustomerEmail nvarchar(120)

Now, imagine that I wanted to add a field to the Customer table indicating if the customer location is handicapped accessible. I have a number of options. First, I could allow the HandicappeeAccessible column to accept nulls or provide a default value. One other option, which would allow applications using the database to insert customers with the new column if they like, would be to change the procedure so that its declaration is as follows:

CREATE PROCEDURE spInsertCustomer
@CustomerName nvarchar(120),
@CustomerAddress nvarchar(120),
@CustomerPhone nvarchar(120),
@CustomerEmail nvarchar(120),
@HandicappedAccessible bit = 0

Since @HandicappedAccessible has a default value, existing programs can continue to call the stored procedure with the original 4 parameters, while new or modified code could add the fifth parameter.

There are other circumstances where you might need to change the interface to a stored procedure. For instance, you might need to retrieve more than a single recordset in a SELECT procedure, or select columns that are named differently. In this case, my solution is to actually create a second stored procedure, with the original name of the stored procedures, with "Ex" or perhaps even "2" appended to the stored procedure name. Thus, older conde continues to work, while newer code can take advantage of the new features added to the "Ex" or "2" version. Sometimes, you may even need to change the internal workings of the original stored procedure so that it's interface is the same as it was before whatever underlying change to the database took place.

So, now that you know how I currently do database "refactoring," it will be interesting to revisit this post after reading a more formal book covering refactoring databases.

Posted by Douglas Reilly at 09:44 AM  Permalink