SQL database refactoring – Introduction

What is database refactoring?

Database refactoring is a change in a database schema with a goal to improve a database design and retain both informational and behavior semantics.

Informational semantics refers to the information contained in a database from the user’s point of view meaning if the data stored in a column is changed external users of the data shouldn’t be affected by the change. For example, changing the format of the telephone numbers from (541) 754-3010 to 5417543010 will simplify the required coding to work with the data, but for the end user the actual information will remain intact.

The same idea applies to retaining behavioral semantic meaning that regardless of the change, the goal is to keep black box functionality the same, meaning that any source code that works with the changed part of database schema must be rewritten to reflect the changes.

Reasons for database refactoring

The most common reason for database refactoring is to improve performance whether due to a bad database design or when dealing with a legacy database.

The first step is to determine that database refactoring is actually needed and whether refactoring will be more work than a potential gain. In cases where there are many external applications that need to be tested, updated, and deployed to support one refactoring it may not be practical to refactor. However, if the database issue is severe enough refactoring may be needed regardless of a number of effected applications.

When the decision to implement refactoring is made, the next step is to choose the most appropriate database refactoring method which also requires detailed analysis and understanding of a problem.

Database refactoring step-by-step

The complete process of refactoring is given in the picture below:

One of the most important steps in the process of a database refactoring is a database testing. The tests should be implemented before, during, and after the change is made. Also, all external applications that access the refactored portion of the database before and after refactoring is applied should be tested as well.

In a single client application environment there may be no need to delay changes. After the refactoring and testing, the source code of the application connected to a database only need to be refactored, tested, and after a successful test all changes can be deployed to a production.

In a multi-application environment every external application is deployed at a different time, so a transition period might be needed, in which both the refactored and the original schema exist and some applications will use the refactored schema while the other will use the original schema. Suppose a database schema with the rename method is refactored. In the transition period the table with an old name will exist in the original schema, and the table with a new name will exist in the refactored schema. A trigger on both tables needs to be introduced, to keep the two database schemas synchronized. After the transition period is over the original schema along with any temporary code (e.g. triggers) can be removed from production.

All changes to a database schema should usually be implemented as a group of small refactoring. When a small change is applied, it needs to be tested to make sure that nothing is broken the possibility that an error occurrence is smaller. The advantage of this approach is that if any error occurs it’s most likely that the bug is in the part of the schema where the last change is made.

ApexSQL Refactor is a SQL Server Management Studio and Visual Studio add-in for database refactoring and SQL code formatting, and offers database and code refactoring methods. All refactoring features are available under the ApexSQL Refactor menu:

Table refactoring methods:

Useful resources:
Introduction To Database Refactoring
Refactoring Databases
What is database refactoring?