Catalog of Database Refactorings

AgileData.org: Techniques for Disciplined Agile Database Development

A database refactoring is a small change to your database schema which improves its design without changing its semantics. This catalog is organized into several categories of database refactoring:

Structural. A change to the table structure of your database schema.
Data quality. A change which improves and/or ensures the consistency and usage of the values stored within the database.
Referential integrity. A change which ensures that a referenced row exists within another table and/or that ensures that a row which is no longer needed is removed appropriately.
Architectural. A change which improves the overall manner in which external programs interact with a database.
Method. A change which improves the quality of a stored procedure, stored function, or trigger.
Non-refactoring transformations. A change which changes the semantics of your database schema by adding new elements to it or by modifying existing elements.

Category	Database Refactorings
Structural Refactorings	Drop Column Drop Table Drop View Introduce Calculated Column Introduce Surrogate Key Merge Columns Merge Tables Move Column Rename Column Rename Table Rename View Replace LOB With Table Replace Column Replace One-to-Many With Associative Table Replace Surrogate Key with Natural Key Split Column Split Table
Data Quality Refactorings	Add Lookup Table Apply Standard Codes Apply Standard Type Consolidate Key Strategy Drop Column Constraint Drop Default Value Drop Non-Nullable Constraint Introduce Column Constraint Introduce Common Format Introduce Default Value Make Column Non-Nullable Move Data Replace Type Code With Property Flags
Referential Integrity Refactorings	Add Foreign Key Constraint Add Trigger for Calculated Column Drop Foreign Key Constraint Introduce Cascading Delete Introduce Hard Delete Introduce Soft Delete Introduce Trigger for History
Architectural Refactorings	Add CRUD Methods Add Mirror Table Add Read Method Encapsulate Table With View Introduce Calculation Method Introduce Index Introduce Read Only Table Migrate Method From Database Migrate Method to Database Replace Method(s) With View Replace View With Method(s) Use Official Data Source
Method Refactorings	Add Parameter Consolidate Conditional Expression Decompose Conditional Extract Method Introduce Variable Parameterize Methods Remove Control Flag Remove Middleman Remove Parameter Rename Method Reorder Parameters Replace Literal With Table Lookup Replace Nested Expression With Guard Clauses Replace Parameter With Specific Methods Split Temporary Variable Substitute Algorithm
Non-Refactoring Transformations	Insert Data Introduce New Column Introduce New Table Introduce View Update Data

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
Agile Testing and Quality Strategies: Discipline Over Rhetoric
Catalog of Database Refactorings
Current State of Data Management: July 2006 Survey Results
Database Refactoring Smells
Database Regression Testing
Easy Database Refactoring (Kasper Graversen)
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 Us Help

We actively work with clients around the world to improve their information technology (IT) practices, typically in the role of mentor/coach, team lead, or trainer. A full description of what we do, and how to contact us, can be found at Scott W. Ambler + Associates.

This site owned by Ambysoft Inc.