Ten Steps to a Robust DB

Software Development

Martin Fowler's now classic Refactoring (Addison-Wesley, 1999) describes a disciplined way to restructure code. A "refactoring," as defined by Fowler, is a simple change to your code that improves its design without altering its behavioral semantics.

In "Refactoring for Fitness" (The Agile Edge, Feb. 2002), I talked about data refactoring—more appropriately, database refactoring. A database schema includes both structural aspects, such as table and view definitions, as well as functional aspects, such as stored procedures and triggers. Be forewarned, however: Because database schemas are more highly coupled than source code, refactoring them can be tricky.

Database refactoring is one way you can normalize your physical database schema after the fact. It supports emergent database design as well as the incremental improvement of existing, legacy database schemas, and it's a fundamental technique adopted by Agile DBAs (a term coined by Peter Schuh in a presentation at SD East in 2001). Database refactoring is a multistep, iterative process, typically undertaken by an application programmer and an Agile DBA working in tandem.

Your Schema Emerges
Let's work through an example. A developer has a new requirement to support international addresses. She believes that the database schema isn't robust enough to support it; therefore, its design needs to be improved. One such improvement is to support alphanumeric postal codes as well as numeric zip codes. In this example, she applies the Replace Column database refactoring (see www.agiledata.org/essays/databaserefactoringcatalog.html for a list of database refactorings) to the Address table to evolve the ZipCode column into a PostCode column.

Here are the 10 steps to implement this refactoring:

1. Verify that a database refactoring is required. Perhaps the required data structure already exists—is there an InternationalSurfaceAddress table or PostalCode column in the SurfaceAddress table? If this isn't the case, the Agile DBA assesses whether the change is actually needed. Can the developer articulate the business requirement motivating the change? Has this developer made good suggestions in the past? What about the refactoring's overall impact? If you must update, test and redeploy 20 other applications to make this change, it might not be viable.

2. Choose the most appropriate database refactoring. You could decide to add a new column to store the postal code, implement a new table for this new type of address, or modify the existing column to accept the new type of data. In this case, the Agile DBA decides on Replace Column.

3. Determine data-cleansing needs. Taking a quick look at the values in the ZipCode column, you may discover the need to cleanse the source data by applying a data-cleansing refactoring before trying this one.

4. Write unit tests. Like code refactoring, database refactoring is enabled by the existence of a comprehensive test suite—you know you can safely change your database schema if you can easily validate that the database still works after the change. The XP community suggests that you write your tests before your business code—and you should do the same with your database schema. UTPLSQL (http://oracle.oreilly.com/utplsql) is a unit-testing tool for Oracle databases and Parasoft's datarecon (www.parasoft.com) that can help with database regression testing. Because test data is critical to your success, you should consider a tool like Datatect (www.datatect.com).

5. Deprecate the original schema. You can't make database schema changes instantly, so you'll have to work with both the old and the new schema while other application teams refactor and redeploy their systems. This parallel running time, or deprecation period, reflects the realities of the sandboxes that you're working in. For example, when the database refactoring is deployed into your development sandbox, the deprecation period may be a few hours. In your project integration sandbox, it may be a few days. In your test/QA and production sandboxes, the deprecation period may be months or even years. When the deprecation period has expired, the original schema, plus any scaffolding code that you wrote to support it, will be removed and the new schema retested.

6. Implement the change. The application developer and Agile DBA work together to make the changes in the development sandbox. Naturally, you'll need to refactor your application code to work with the new database schema. Perform an initial performance analysis to determine the potential impact—for some refactorings, you may decide to back out because of poor performance. Don't forget to check that the changed portion of the schema follows corporate database development guidelines, which, at a minimum, should address naming and documentation standards.

7. Update your database management scripts. These scripts are used to modify your database schema. Your database change log implements all database schema changes in the order that they were applied throughout the course of a project. When you're implementing a database refactoring, you include only the immediate changes in this log. When applying the Replace Column database refactoring, you include the data definition language (DDL) for adding the PostCode column and the DDL to implement the triggers to maintain the values between the PostCode and ZipCode columns during the deprecation period. Your update log contains the source code for future changes to the database schema to be run after the deprecation period. In our example, this is the source code required to remove the ZipCode column and the triggers we introduced. Finally, the data migration log contains the data manipulation language (DML) to reformat or cleanse the source data throughout the course of your project. In our example, this includes any code to improve the quality of the values in the ZipCode column.

8. Run your regression tests. Once you've refactored your application code and database schema, you must run your regression test suite. Testing activities include the installation or generation of test data, running of the tests themselves, comparison of the actual test results with the expected results, and resetting the database back the way you found it. Because successful tests find problems, you'll need to rework your code and schema.

9. Document the refactoring. Databases are shared resources, so the Agile DBA needs to communicate and negotiate the changes with all parties.

10. Version control your work. Agile developers must put all of their work under configuration management control. Items to check in a change-tracking system include any DDL that you've created, change scripts, data migration scripts, test data, test cases, test data generation code, documentation and models.

A Little Design Goes a Long Way
Database refactoring is a skill that all agile software developers should have, but it isn't the only one. Ideally, you should get your database schema right the first time, which often involves doing some modeling—just avoid a Big Design Up Front approach when doing so.

Scott Ambler is a senior consultant with Ronin International Inc. His latest book is The Elements of UML Style (Cambridge University Press, 2002).