Catalog of Method Database Refactorings

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

A method database refactoring is a change which improves the quality of a stored procedure, stored function, or trigger so as to improve your database design without changing its semantics.

Refactoring

Example

Add Parameter. An existing method needs information that was not passed in before.

Consolidate Conditional Expression. Combine sequence of conditional tests into a single conditional expression and extract it.

Before

After

CREATE OR REPLACE FUNCTION GetAccountAverageBalance
( inAccountID IN NUMBER)
RETURN NUMBER;
AS
averageBalance := 0;
BEGIN
IF inAccountID > 10000 THEN
RETURN 0;
END IF;

IF inAccountID = 123456 THEN
RETURN 0;
END IF;

IF inAcountID = 987654 THEN
RETURN 0;
END IF;

-- Code to calculate the average balance
RETURN averageBalance;
END;

CREATE OR REPLACE FUNCTION GetAccountAverageBalance
( inAccountID IN NUMBER)
RETURN NUMBER;
AS
averageBalance := 0;
BEGIN
IF inAccountID < 10000 || inAccountID = 123456 || inAcountID = 987654 THEN
RETURN 0;
END IF;

-- Code to calculate the average balance
RETURN averageBalance;
END;

Decompose Conditional. Extract methods from the condition.

Before

After

CREATE OR REPLACE FUNCTION CalculateInterest
( inBalance IN NUMBER )
RETURN NUMBER;
AS
lowBalance NUMBER;
highBalance NUMBER;
lowInterestRate NUMBER;
highInterestRate NUMBER;

BEGIN
lowBalance := GetLowBalance();
highBalance := GetHighBalance();
lowInterestRate := GetLowInterestRate();
highInterestRate := GetHighInterestRate();

IF inBalance < lowBalance THEN
RETURN 0;
END IF

   IF inBalance >= lowBalance && inBalance <= highBalance THEN
    RETURN inBalance * lowInterestRate;
ELSE
    RETURN inBalance * highInterestRate;
END IF;
END;

CREATE OR REPLACE FUNCTION CalculateInterest
( inBalance IN NUMBER )
RETURN NUMBER;
AS
BEGIN

IF BalanceIsInsufficient( inBalance ) THEN
RETURN 0;
END IF

IF IsLowInterestBalance( inBalance ) THEN
RETURN CalculateLowInterest( inBalance );
ELSE
RETURN CalculateHighInterest( inBalance );
END IF;
END;

Extract Method. Turn the code fragment into a method whose name explains the purpose of the method.

Before

After

CREATE OR REPLACE FUNCTION CalculateAccountInterest
( inAccountID IN NUMBER,
inStart IN DATE,
inEnd IN DATE )
RETURN NUMBER;
AS
medianBalance NUMBER;
startBalance NUMBER;
endBalance NUMBER;
interest := 0;
BEGIN
BEGIN
  -- Determine the starting balance
  SELECT Balance INTO startBalance
    FROM DailyEndBalance
    WHERE AccountID = inAccountID && PostingDate = inStart;
  EXCEPTION WHEN NO_DATA_FOUND THEN
      startBalance := 0;

  -- Determine the ending balance
     SELECT Balance INTO endBalance
   FROM DailyEndBalance
     WHERE AccountID = inAccountID && PostingDate = inEnd;
     EXCEPTION WHEN NO_DATA_FOUND THEN
     endBalance := 0;
   END;

   medianBalance := ( startBalance + endBalance ) / 2;
   IF medianBalance < 0 THEN
      medianBalance := 0;
   END IF;
IF medianBalance >= 500 THEN
      interest := medianBalance * 0.01;
  END IF;

RETURN interest;
END;

CREATE OR REPLACE FUNCTION CalculateAccountInterest
( inAccountID IN NUMBER,
inStart IN DATE,
inEnd IN DATE )
RETURN NUMBER;
AS
medianBalance NUMBER;
startBalance NUMBER;
endBalance NUMBER;
BEGIN
startBalance := GetDailyEndBalance ( inAccountID, inStart );
endBalance:= GetDailyEndBalance ( inAccountID, inEnd );
medianBalance := CalculateMedianBalance ( startBalance, endBalance );
RETURN CalculateInterest ( medianBalance );
END;

Introduce Variable. Put the result of the expression, or parts of the expression, in a temporary variable with a name that explains the purpose.

Before

After

CREATE OR REPLACE FUNCTION DetermineAccountStatus
( inAccountID IN NUMBER,
inStart IN DATE,
inEnd IN DATE )
RETURN VARCHAR;
AS
lastAccessedDate DATE;
BEGIN

-- Some code to calculate lastAccessDate

   IF ( inDate < lastAccessDate && outdate > lastAccessDate )
    && ( inAccountID > 10000 )
    && ( inAccountID != 123456 && inAcountID != 987654) THEN
   -- do something

END IF;
-- do another thing
END;

CREATE OR REPLACE FUNCTION DetermineAccountStatus
( inAccountID IN NUMBER,
    inStart IN DATE,
    inEnd IN DATE )
RETURN VARCHAR;
AS
lastAccessedDate DATE;
isBetweenDates BOOLEAN;
isValidAccountID BOOLEAN;
isNotTestAccount BOOLEAN
BEGIN
-- Some code to calculate lastAccessDate
isBetweenDates := inDate < lastAccessDate && outdate > lastAccessDate;
isValidAccountID := inAccountID > 100000;
isNotTestAccount := inAccountID != 123456 && inAcountID != 987654;
IF isBetweenDates && isValidAccountID && isNotTestAccount THEN
    -- do something
END IF;
-- do another thing
END;

Parameterize Methods. Create one method that uses a parameter for the different values.

Remove Control Flag. Use remove or break instead of a variable acting as a control flag.

Before	After
DECLARE controlFlag := 0; anotherVariable := 0; BEGIN WHILE controlFlag = 0 LOOP -- Do something IF anotherVariable > 20 THEN controlFlag = 1; ELSE -- Do something else END IF; END LOOP; END;	DECLARE anotherVariable := 0; BEGIN WHILE anotherVariable <= 20 LOOP -- Do something -- Do something else END LOOP; END;

Remove Middleman. Get the caller to call the method directly.

Before	After
CREATE OR REPLACE PROCEDURE AProcedure parameter1 IN NUMBER; … parameterN IN VARCHAR; AS BEGIN EXECUTE AnotherProcedure ( parameter1, …, parameterN ); END;

Remove Parameter. Remove a parameter no longer used by the method body.

Rename Method. Rename an existing method with a name that explains the purpose.

Reorder Parameters. Change the order of the parameters of a method.

Replace Literal With Table Lookup. Replace Code constants with values from database tables.

Before	After
CREATE OR REPLACE FUNCTION CalculateInterest ( inBalance IN NUMBER ) RETURN NUMBER; AS interest := 0; BEGIN IF inBalance >= 500 THEN interest := medianBalance * 0.01; END IF; RETURN interest; END;	CREATE OR REPLACE FUNCTION CalculateInterest ( inBalance IN NUMBER ) RETURN NUMBER; AS interest := 0; minimumBalance NUMBER; interestRate NUMBER; BEGIN minimumBalance := GetMinimumBalance(); interestRate := GetInterestRate(); IF inBalance >= minimumBalance THEN interest := medianBalance * interestRate; END IF; RETURN interest; END;

Replace Nested Expression With Guard Clauses. Remove nested if conditions with a series of separate IF statements.

Before

After

BEGIN
IF condition1 THEN
    -- do something 1
ELSE
    IF condition2 THEN
      -- do something 2
    ELSE
      IF condition3 THEN
        -- do something 3
      END IF;
    END IF;
END IF;
END;

BEGIN
IF condition1 THEN
-- do something 1
RETURN;
END IF;

IF condition2 THEN
-- do something 2
RETURN;
END IF;

IF condition3 THEN
-- do something 3
RETURN;
END IF;
END;

Replace Parameter With Specific Methods. Create a separate method for each value of the parameter.

Split Temporary Variable. Make a separate temporary variable for each assignment.

Before	After
DECLARE aTemporaryVariable := 0; farenheitTemperature := 0; lengthInInches := 0; BEGIN -- retrieve farenheitTemperature aTemporaryVariable := (farenheitTemperature – 32 ) * 5 / 9; -- do something -- retrieve lengthInInches aTemporaryVariable := lengthInInches * 2.54; -- do something END;	DECLARE celciusTemperature := 0; farenheitTemperature := 0; lengthInCentimeters := 0; lengthInInches := 0; BEGIN -- retrieve farenheitTemperature celciusTemperature := (farenheitTemperature – 32 ) * 5 / 9; -- do something -- retrieve lengthInInches lengthInCentimeters := lengthInInches * 2.54; -- do something END;

Substitute Algorithm. Replace the body of the method with the new algorithm.

Recommended Books

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.

Working Effectively With Legacy Code describes techniques for refactoring and testing existing, legacy code. Few teams have the luxury of building everything from scratch, instead they must work from an existing base of code, or minimally integrate with other legacy systems. In this book Michael Feathers covers the fundamental techniques which agile developers need to effectively work in these sorts of environments. You don’t need to stop all development and rework your legacy code, instead you can ease into it over time, and this book shows you how to do that successfully.

References and Suggested Online Readings

Agile/Evolutionary Data Modeling
Agile Database Best Practices
Catalog of Database Refactorings
Current State of Data Management: July 2006 Survey Results
Database Refactoring Smells
Database Regression Testing
On Relational Theory
The Process of Database Refactoring
Refactoring Databases (Objectview Issue #10, 3.4 Meg PDF)
The Rename Column Database Refactoring: A Complete Example
Ruby on Rails -- Active Record Migration
Sundog Database Refactoring Tool
Visual Studio Team Edition for Database Professionals
Whence Data Management? explores the current state of database management, including database refactoring
Whence Data Quality? explores the current state of data quality

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 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.

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.