Catalog of Database Refactorings: Method Refactorings
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 IFIF 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
BEGINIF BalanceIsInsufficient( inBalance ) THEN
RETURN 0;
END IFIF 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 lastAccessDateIF ( inDate < lastAccessDate && outdate > lastAccessDate )
&& ( inAccountID > 10000 )
&& ( inAccountID != 123456 && inAcountID != 987654) THEN
— do somethingEND 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. |
|
Source
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.
Recommended Reading
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.