The Agile Data (AD) Method

The Rename Column Database Refactoring: A Complete Description

database refactoring is a simple change to a database which improves its design without changing its semantics. In other words a database refactoring neither adds anything nor does it take anything away, it merely improves it. This article provides a complete description of the Rename Column database refactoring. This description has been excerpted from the book Refactoring Databases: Evolutionary Database Design. This description is typical of the 70 which appear in the book. The code presented is Java and Oracle PLSQL, but we could have chosen any major application programming language or RDBMS and implemented a similar solution. In other words, this technique isn’t specific to Oracle and Java.

Rename Column

Rename an existing table column.

Figure 1. Renaming the Customer.FName column.

Motivation

The primary reasons to apply Rename Column are to increase the readability of your database schema, to conform to accepted database naming conventions in your enterprise, or to enable database porting. For example, when you are porting from one database product to another, you may discover that the original column name cannot be used because it is a reserved key word in the new database.

Potential Trade-Offs

The primary trade-off is the cost of refactoring the external applications that access the column versus the improved readability and/or consistency provided by the new name.

Schema Update Mechanics

To rename a column, you must do the following:

  1. Introduce the new column. In Figure 1, we first add FirstName to the target table via the SQL command ADD COLUMN.
  2. Introduce a synchronization trigger. As you can see in Figure 1, you require a trigger to copy data from one column to the other during the transition period. This trigger must be invoked by any change to the data row.
  3. Rename other columns. If FName is used in other tables as (part of) a foreign key, you may want to apply Rename Column recursively to ensure naming consistency. For example, if Customer.CustomerNumber is renamed as Customer.CustomerID, you may want to go ahead and rename all instances of CustomerNumber in other tables. Therefore, Account.CustomerNumber will now be renamed to Account.CustomerID to keep the column names consistent.

The following code depicts the DDL to rename Customer.FName to Customer.FirstName and creates the SynchronizeFirstName trigger that synchronizes the data during the transition period.

ALTER TABLE Customer ADD FirstName VARCHAR(40);

 

COMMENT ON Customer.FirstName “˜Renaming of FName column, finaldate = November 14 2007′;

COMMENT ON Customer.FName “˜Renamed to FirstName, dropdate = November 14 2007′;

UPDATE Customer SET FirstName = FName;

 

CREATE OR REPLACE TRIGGER SynchronizeFirstName

BEFORE INSERT OR UPDATE

ON Customer

REFERENCING OLD AS OLD NEW AS NEW

FOR EACH ROW

DECLARE

BEGIN

IF INSERTING THEN

IF :NEW.FirstName IS NULL THEN

:NEW.FirstName := :NEW.FName;

END IF;

IF :NEW.Fname IS NULL THEN

:NEW.FName := :NEW.FirstName;

END IF;

END IF;

 

IF UPDATING THEN

IF NOT(:NEW.FirstName=:OLD.FirstName) THEN

:NEW.FName:=:NEW.FirstName;

END IF;

IF NOT(:NEW.FName=:OLD.FName) THEN

:NEW.FirstName:=:NEW.FName;

END IF;

END IF;

END;

The following DDL removes the original column and trigger after the transition period ends.

–After Nov 14 2007

DROP TRIGGER SynchronizeFirstName;

ALTER TABLE Customer DROP COLUMN FName;

Data Migration Mechanics

You need to copy all the data from the original column into the new column, in this case from FName to FirstName via the refactoring Move Data.

Access Program Update Mechanics

External programs that reference Customer.FName must be updated to reference columns by its new name. You should simply have to update any embedded SQL and/or mapping meta data. The following Hibernate mapping file depicts the “before mapping”.

//Before mapping

<hibernate-mapping>

<class name=”Customer” table=”Customer”>

<id name=”id” column=”CUSTOMERID”>

<generator class=”CustomerIdGenerator”/>

</id>

<property name=”fName”/>

</class>

</hibernate-mapping>

The transition period mapping:

//Transition mapping

<hibernate-mapping>

<class name=”Customer” table=”Customer”>

<id name=”id” column=”CUSTOMERID”>

<generator class=”CustomerIdGenerator”/>

</id>

<property name=”firstName”/>

</class>

</hibernate-mapping>

The resulting mapping (which is the same as the transition mapping):

//After mapping

<hibernate-mapping>

<class name=”Customer” table=”Customer”>

<id name=”id” column=”CUSTOMERID”>

<generator class=”CustomerIdGenerator”/>

</id>

<property name=”firstName”/>

</class>

</hibernate-mapping>

 

Related Resources

 


Recommended Reading

Choose Your WoW! 2nd Edition
This book, Choose Your WoW! A Disciplined Agile Approach to Optimizing Your Way of Working (WoW) – Second Edition, is an indispensable guide for agile coaches and practitioners. It overviews key aspects of the Disciplined Agile® (DA™) tool kit. Hundreds of organizations around the world have already benefited from DA, which is the only comprehensive tool kit available for guidance on building high-performance agile teams and optimizing your WoW. As a hybrid of the leading agile, lean, and traditional approaches, DA provides hundreds of strategies to help you make better decisions within your agile teams, balancing self-organization with the realities and constraints of your unique enterprise context.

 

I also maintain an agile database books page which overviews many books you will find interesting.