The Rename Column Database Refactoring: A Complete Description

www.agiledata.org: Techniques for Successful Evolutionary/Agile Database Development

A 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:

Introduce the new column. In Figure 1, we first add FirstName to the target table via the SQL command ADD COLUMN.
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.
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;

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;

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>

</id>

</class>

</hibernate-mapping>

The transition period mapping:

//Transition mapping

<hibernate-mapping>

</id>

</class>

</hibernate-mapping>

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

//After mapping

<hibernate-mapping>

</id>

</class>

</hibernate-mapping>

Suggested Readings

This book describes the philosophies and skills required for developers and database administrators to work together effectively on project teams following evolutionary software processes such as Extreme Programming (XP), the Rational Unified Process (RUP), the Agile Unified Process (AUP), Feature Driven Development (FDD), Dynamic System Development Method (DSDM), or The Enterprise Unified Process (EUP). In March 2004 it won a Jolt Productivity award.

This book describes, in detail, how to refactor a database schema to improve its design. The first section of the book overviews the fundamentals evolutionary database techniques in general and of database refactoring in detail. More importantly it presents strategies for implementing and deploying database refactorings, in the context of both "simple" single application databases and in "complex" multi-application databases. The second section, the majority of the book, is a database refactoring reference catalog. It describes over 60 database refactorings, presenting data models overviewing each refactoring and the code to implement it.

This book presents a full-lifecycle, agile model driven development (AMDD) approach to software development. It is one of the few books which covers both object-oriented and data-oriented development in a comprehensive and coherent manner. Techniques the book covers include Agile Modeling (AM), Full Lifecycle Object-Oriented Testing (FLOOT), over 30 modeling techniques, agile database techniques, refactoring, and test driven development (TDD). If you want to gain the skills required to build mission-critical applications in an agile manner, this is the book for you.

Acknowledgements

I'd like to thank Steven Shaw for pointing out a bug in the original version of the transition phase mapping.

Let Me Help

I actively work with clients around the world to improve their information technology (IT) practices as both a mentor/coach and trainer. A full description of what I do, and how to contact me, can be found here.