Catalog of Architectural Database Refactorings

www.agiledata.org: Techniques for Successful Evolutionary/Agile Database Development

An architectural database refactoring is a change which improves the overall manner in which external programs interact with a database so as to improve your database design without changing its semantics.

Refactoring	Example
Add CRUD Methods. Introduce stored procedures (methods) to implement the creation, retrieval, update, and deletion (CRUD) of the data representing a business entity.
Add Mirror Table. Create a mirror table, an exact duplicate of an existing table in one database, in another database.
Add Read Method. Introduce a method, in this case a stored procedure, to implement the retrieval of the data representing zero or more business entities from the database.
Encapsulate Table With View. Wrap access to an existing table with a view.
Introduce Calculation Method. Introduce a new method, typically a stored function, which implements a calculation that uses data stored within the database.
Introduce Index. Introduce a new index of either unique or non-unique type.
Introduce Read Only Table. Create a read only data store based on existing tables in the database.
Migrate Method From Database. Rehost an existing database method (a stored procedure, stored function, or trigger) in the application(s) which currently invoke it.
Migrate Method to Database. Rehost existing application logic in the database.
Replace Method(s) With View. Create a view based on one or more existing database methods (stored procedures, stored functions, or triggers) within the database.
Replace View With Method(s). Replace an existing view with one or more existing methods (stored procedures, stored functions, or triggers) within the database.
Use Official Data Source (directly). Use the official data source for a given entity, instead of the current one which you are using.
Use Official Data Source (via replication). Use the official data source for a given entity, instead of the current one which you are using.

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
Catalog of Database Refactorings
Current State of Data Management: July 2006 Survey Results
Database Refactoring Smells
Database Regression Testing
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 Me Help

I actively work with clients around the world to improve their information technology (IT) practices as both a mentor/coach and trainer. A full description of what I do, and how to contact me, can be found here.