Refactoring Databases: Evolutionary Database Design

Refactoring Databases describes how to safely evolve an existing database to support the new needs of your customers or to address existing data technical debt. A database refactoring is a small change to your database schema which improves its design without changing its semantics (e.g. you don’t add anything nor do you break anything). The process of database refactoring is the evolutionary improvement of your database schema so as to improve your ability to support the new needs of your customers, support evolutionary software development, and to fix existing legacy database design problems.

The book describes refactoring databases from the point of view of:

  1. Technology. It includes full source code for how to implement each refactoring at the database level and for most refactorings we show how the application would change to reflect the change in the database. Our code examples are in Oracle, Java, and Hibernate meta-data (the refactorings are easy to translate to other environments, and sometimes we discuss vendor-specific features which simplify some refactorings).

  2. Process. It describes in detail the process of database refactoring in both the simple situation of a single application accessing the database as well as the situation of the database being accessed by many programs, many of which are out of the scope of your authority. The technical examples assume the latter situation, so if you’re in the simple situation you may find some of our solutions to be a little more complicated than you need (lucky you!).

  3. Culture. Although it is technically simple to implement individual refactorings, and clearly possible (albeit a little complicated) to adapt your internal processes to support database refactoring, the fact is that cultural challenges within your organization will likely prove to be the most difficult hurdle to overcome.

Published: January 2006

Status: Available

Where to Buy This Book:

Book Organization

This book is organized into two sections:

Part 1: Evolutionary database development and refactoring databases. This section covers 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.

  • Chapter 1: Evolutionary Database Development. Overviews the fundamentals of evolutionary development and the techniques which support it. It summarizes refactoring, database refactoring, database regression testing, evolutionary data modeling via an agile model driven development (AMDD) approach, configuration management of database assets, and the need for separate developer sandboxes.
  • Chapter 2: Database Refactoring. Explores in detail the concepts behind database refactoring and why it can be so hard to do in practice. It also works through a database refactoring example in both a “simple” single application environment as well as in a complex, multi-application environment.
  • Chapter 3: The Process of Database Refactoring. Describes in detail the steps required to refactor your database schema in both simple and complex environments. With single application databases you have much greater control over your environment and as a result need to do far less work to refactor your schema. In multi-application environments you need to support a transition period where your database supports both the old and new schemas in parallel, enabling the application teams to update and deploy their code into production.
  • Chapter 4: Deploying Into Production. Describes the process behind deploying database refactorings into production. This can be particularly challenging in a multi-application environment because the changes of several teams needs to be merged and tested.
  • Chapter 5: Database Refactoring Strategies. Summarizes some of the effective practices which we’ve discovered over the years when it comes to refactoring database schemas. We also float a couple of ideas that we’ve been meaning to try out but haven’t been able to do so yet.

Part 2: Database refactoring reference catalog. This section, the majority of the book, describes over 65 database refactorings, presenting data models overviewing the each refactoring and the code to implement it.

  • Chapter 6: Structural Refactorings. Structural refactorings, as the name implies, change the table structure of your database schema. They include: 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, and Split Table.
  • Chapter 7: Data Quality Refactorings. Data quality refactorings improve and/or ensure the consistency and usage of the values stored within the database. They include: 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, and Replace Type Code With Property Flags.
  • Chapter 8: Referential Integrity Refactorings. Referential integrity refactorings are changes which ensure that a referenced row exists within another table and/or that ensures that a row which is no longer needed is removed appropriately. They include: Add Foreign Key Constraint, Add Trigger for Calculated Column, Drop Foreign Key Constraint, Introduce Cascading Delete, Introduce Hard Delete, Introduce Soft Delete, and Introduce Trigger for History.
  • Chapter 9: Architectural Refactorings. Architectural refactorings are changes which improve the overall manner in which external programs interact with a database. The architectural refactorings are: Add CRUD Methods, Add Mirror Table, Add Read Method, Encapsulate Table With View, Introduce Calculation Stored Procedure, Introduce Index, Introduce Read Only Table, Migrate Method From Database, Migrate Method to Database, Replace Method(s) With View, Replace View With Method(s), and Use Official Data Source.
  • Chapter 10: Method Refactorings. A method refactoring improves the quality of a stored procedure, stored function, or trigger. The method refactorings are: 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, and Substitute Algorithm.
  • Chapter 11: Transformations. Transformations are changes which change the semantics of your database schema by adding new features to it. The transformations are: Insert Data, Introduce New Column, Introduce New Table, Introduce View, and Update Data.

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.