Catalog of Database Refactorings – Data Quality Refactorings

A data quality database refactoring is a change which improves and/or ensures the consistency and usage of the values stored within the database so as to improve your database design without changing its semantics.

Refactoring Example
Add Lookup Table. Create a lookup table for an existing column.


Apply Standard Codes. Apply a standard set of code values to a single column to ensure that it conforms to the values of similar columns stored elsewhere in the database.


Apply Standard Type. Ensure that the data type of a column is consistent with the data type of other similar columns within the database.


Consolidate Key Strategy. Choose a single key strategy for an entity and apply it consistently throughout your database.


Drop Column Constraint. Remove a column constraint from an existing table.


Drop Default Value. Remove the default value that is provided by a database from an existing table column.


Drop Non-Nullable Constraint. Change an existing non nullable column such that it accepts null values.


Introduce Column Constraint. Introduce a column constraint in an existing table.


Introduce Common Format. Apply a consistent format to all the data values in an existing table column.


Introduce Default Value. Let the database provide a default value for an existing table column.


Make Column Non-Nullable. Change an existing column such that it does not accept any null values.


Move Data. Move the data contained within a table, either all or a subset of its columns, to another existing table.


Replace Type Code With Property Flags. Replace a code column with individual property flags, usually implemented as Boolean columns, within the same table column.




Choose Your WoW! 2nd Edition
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

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.