Scott Ambler and Pramod Sadalage wrote Refactoring Databases, they say, "to share their experiences and techniques at evolving database schemas via refactoring". The book, particularly in the thorough list of refactorings detailed in later chapters, reveals them to be experienced users of, and writers about, agile development approaches. Their core premise is that data and databases must evolve in the same way as code does - that is incrementally.

They argue persuasively that a big-bang, up-front approach to database design is unacceptable in a modern environment. There is simply too much change and too much uncertainty in the business world for this to be realistic. The basic techniques for evolutionary database design include refactoring (the topic of the book), evolving the data model, database regression testing and configuration management and developer sandboxes. This more evolutionary approach is going to be a big shock for many data professionals, something the authors note, but I think the need for effective evolution and ongoing development of applications and thus their databases is compelling. "Change time", the period after an application (or database) is first deployed is bar far the majority of the life of an application. Techniques that help you cope with this, like database refactoring, are a good thing. Database refactoring as described in the book, is part of an evolutionary approach and with development teams taking on SCRUM, XP and other agile methods it is more important than ever for database teams to do likewise. Many data professionals will likely have the same knee-jerk reaction I did when first approaching this - Why not just get it right up front? But if you believe that agile model-driven development is here to stay for code then you have to accept the need for the same approach to database design.

Martin Fowler's original book Refactoring: Improving the Design of Existing Code made the point that a refactoring must retain the behavioral semantics of the code and this is just as true in databases. The authors take great pains to explain refactoring in enough detail that it you can apply it constantly to keep the database as clean and easy to modify as possible. They emphasize repeatedly the value of test-driven or test first development - even in database design and deployment. The authors stress the importance of testing, especially regression testing, of all the components that access a database when refactoring it. They advise making refactoring efforts small as well as test-driven. They point out that refactoring should be done as a series of small steps and that database develops must not succumb to the temptation to combine refactorings into larger, more complex efforts. The need to treat database designs, and even data, as artifacts subject to change control comes through loud and clear.

The concept of a potentially very long transition period in which both the old and new schemas are supported is a particularly good one. I worry about the organizational dynamics of having the old schema removed by a new team that does not remember the original refactoring but nothing else seems rational in a typical environment where many applications run against the same database. I also liked the paranoia of the authors, for instance in their suggestion to always run regression tests BEFORE refactoring to make sure the database is actually as you think it is! While the book focused on refactoring, many of the hints and suggestions would be good for implementing real changes in business policy.

The book is a surprisingly easy read for such a potentially dense subject. The book starts by describing the fundamentals of evolutionary database development and the basics of refactoring. A process overview, deployment notes and some best practices follow. These initial chapters, designed to be read in sequence, introduce and explain the topic well and have a nice little "What you have learned section" at the end. There were many worthwhile asides in the book as it covers these topics and after these introductory chapters, the book then goes (somewhat abruptly) into a series of chapters on various kinds of refactoring - structural, data quality, referential integrity, architectural, method and transformations. These chapters take a series of very specific refactorings. The potential motivation, tradeoffs and implementation mechanics are defined for each. The refactorings are self-contained and, while this makes reading them as a set somewhat repetitive, it should make the book a much better reference guide for actual users.

The book did not really touch on how you should consider data and database designs used in analytic models or the potential value of using business rules, but these are minor quibbles. The book is well written, full of great examples and gives lots of good advice.

This book is helpful for applying the basic coding practices that have been so useful in keeping code in good shape to the messy world of the DBA. It was always shocking to me how poor most DBAs were at making changes to their schema and the data. The great ones I have worked with already did a lot of stuff in this book- use scripts, etc. The other ones need to learn- and developers need to learn to if they are working sans DBA.

This book is a much needed exploration on the subject. It tries to categorize those operations that developers and DBAs do on a database, who, for various reasons, must address a specific problem, need. I only gave it three stars because it is somewhat insufficient. It also doesn't make much of a distinction between a database in development and one in production. In the latter case, it is really difficult to make changes when there is already a data structure in place with data, being updated constantly by users, and plans to migrate to a different data model while a system is in production is really not for the faint of heart. What I am really loooking for is a thick book of bad designs that, for various reasons (unclear or evolving requirements, political), a database model presents itself with a bunch of problems, real problems and not just theoretical, and the ways a DBA and developers came about after a big pow-wow on how to solve it.

Sorry, but I can not recommend this book to anyone who is looking for an agile way of handling schema evolution.

To me the style of writing is boring and in no way adaptable to a real word model.

Much more beautiful code is being written as a result of Fowler's Refactoring; the Gang of Four's Patterns; Beck's Test Driven Development; etc. Unfortunately the database development community either hasn't applied these principles & practices or hasn't bothered to read about them.

So Scott and Pramod have brought the mountain to Mohammed with Refactoring Databases. Most database developers I interact with are loathe to change a database design. It's scary and risky! As a result most database applications continue to incur increasing technical debt, entropy, and ugliness. In my work as an Agile Coach and Agile Data Warehouse developer I hardly ever get to see a truly beautiful data model in production. This book, when applied, will go a long way toward helping database developers create beauty and avoid the scariness of changing data models.

This book is a must read for database developers, and I reserve "must read" status for very few books. Next thing we need is a database patterns book.

First let me say that Messrs. Ambler and Sadalage did a very good job with this book. The database design advice is generally good, reflecting good practices: Make sure that your columns are in the right table, for example. Rename columns and views to make them more meaningful. Divide and combine tables so that they more closely represent their meaning in the application.

Not only that, they are providing a valuable service to those people who followed their other advice to develop systems as quickly as possible, with little regard for thinking them through in advance. Clearly if you have a database that you created in the agile environment, it is valuable to have this advice on how to fix (er, refactor) it.

But the point is made at the beginning of the book that, if the database is already in production with numerous applications using it, this will be very hard. It will be time-consuming. "Typical transition periods last for several quarters, if not years." (Page 34)

One then has to ask the question: Why not, instead of waiting until your database is a mess, requiring time-consuming changes, don't you buy this book before you start, and take some of refactoring time to think about the design, applying these principles before you create the database? Perhaps you can then skip the refactoring altogether.

I've done a lot of refactoring and restructuring programs and always had to deal with the problems involved with changing the layout of persistent storage. This book describes solutions for those issues I encountered. It explores some tough questions, like how to refactor a database used by multiple applications, only one of which is currently undergoing modification. I recommend it to anyone involved with the database side of systems, whether an administrator or a programmer who uses one.

As any Agile consultant will attest to, DBA's are the last great hold out when it comes to Agile adoption. Many people have simply written off the database as this great monolithic giant that can not be moved.

Pramod and Scott show us that it is possible. The database is just another piece of the puzzle. This book walks you through just about every obstacle you could encounter as a DBA trying to improve what seems like an inflexible monolith.

This book does a fantastic job of being very pragmatic in their approach to the improvements that will be made. Several instances describe processes that may take over a year to implement in order to account for all aspects involved. You can tell that both Pramod and Scott are speaking from experience.

This is an excellent book that, in my opinion, serves two purposes. First, it is a compendium of well thought-out ways to evolve a database design. Each refactoring includes descriptions of why you might make this change, tradeoffs to consider before making it, how to update the schema, how to migrate the data, and how applications that access the data will need to change. Some of the refactorings are simple ones that even the most change-resistant DBAs will have used in the past ("Add index"). Most others (such as "Merge tables" or "Replace LOB with Table") are ones many conventional thinking DBAs avoid, even to the detriment of the applications their databases support.

This brings me to the second purpose of this book. Many DBAs view their jobs as protectors of the data. While that is admirable, they sometimes forget that they are part of a software development team whose job is to provide value to the organization through the development of new (and enhancement of existing) applications. One of the best DBAs I ever worked with viewed himself as a "Data Valet." He said his job was to make sure the data was presented to applications when and where they wanted and to protect the doors from getting dinged while under his care. Through its first five chapters and then the refactorings that follow, this book will help DBAs expand their view of their role in the organization from one of simply protecting data to one of enhancing the value of data to the organization.

This book is one that you'll keep on your reference shelf for many years to come. Highly recommended.

This is in response to the review concerned about the minimal coverage of database testing. When Pramod and I wrote the book we decided to focus just on database refactoring itself, which I hope you'll agree that we did. In Chapter 1 we do in fact point out that database regression testing is an important enabler of database refactoring, as is configuration management, agile data modeling, and developer sandboxes. We didn't invest much time on those subjects because we wanted to remain on topic.

We have gone into a fair bit of detail with the individual refactorings. In particular, we provide the source code required to implement each one and show Java examples of how application source would potentially change as the result of the database being refactored. Each refactoring is described as a stand-alone reference, the end result being that if you were to read the book from cover to cover you would see several common themes, such as migrating data and deprecating the original schema, repeated throughout. But, who is going to read reference material cover to cover?

As you know, there are several good books written about configuration management and therefore covering that in detail didn't make much sense to us. The topic of developer sandboxes, although important, likely only rates an article or two. Nobody has written a book specifically about Agile Data Modeling, although with a little bit of searching here on Amazon I have no doubt that you can find a good book or two about Agile Modeling ;-). I definitely agree that a book is needed about database regression testing and I'm thinking seriously about writing one myself.

If you are interested in those topics, I highly suggest that you visit the Agile Data site where I've written extensively about them already. Otherwise, I hope that people find Refactoring Databases to be a comprehensive discussion of this new, leading edge technique.

Despite the countless uses of "Agile" and "Agility" in the book, the approach described in "Refactoring Databases" feels rather clumsy and involved and in parts reads more like a descriptive than a heuristic process. That does not mean that I find the techniques and rules useless but in my experience you can do with much less "ado" in many projects. Moreover, the authors completely forgo the intricacies of database testing, a topic which hasn't been fully covered by any other books I know of and which nonetheless is stated as a fundamental pillar of db refactoring.

Wow! In a word....Wow!

Ambler and Sadalage have brought to the database world a truly important and sorely needed concept. Yes, data is critical to all applications. An application with process only is an application that does a lot, and accomplishes nothing.

The world of software development is finally starting getting smart with code refactoring. It is about time that we are starting a similar discussion regarding data refactoring as well. To think that our data models would start and stay perfect, while our code and our models would change over time is wrong.

Ambler and Sadalage recognize that and offer practical refactorings and transformations (including SQL!), to help any team, large or small, get back on track. This seminal book will be used for years to come in our industry. It is concise, complete, and undoubtedly field tested. Isn't this all we could hope for in our own systems under development....? These men are not spouting ideas from the tops of mountains, ideas conceived in ivory towers. They have "been there...done that" and we need to listen....now.

I truly look forward to a future book from Scott Ambler. I know that he will certainly have a look at Process Refactoring before long. Have any of us worked on a project where the initial process defined was the final one? Didn't think so ;-)

Support for database refactoring has been needed for some time. All projects that use a database, whether Agile or not, wind up with the need to improve the structure of the database. This book describes about 70 refactorings, telling you how to do it, what the pros and cons are, and even how to transition the changes over time, to give your applications time to convert. Good stuff!

I've put a longer review on the XProgramming web site, but the bottom line is that if your database is getting crufty, this book can help.

On a recent project, I was brought in to develop an enterprise-class system, end-to-end (business requirements through deployment). This was a unique project in that, I was a one-man show, despite the fact that it ran in a clustered environment and required 99.9% uptime due to the financial impact of this system. One thing I noticed throughout the project was that I was able to start with an initial/minimal database design and refactor it over time, as the application was being built. I kept thinking the whole time how nice it felt to be able to tweak, fix, optimize, drop stuff in the database, as needed, along the ways and not be locked down with a design that was done using a big design up front (BDUF) approach.

The BDUF is prevalent in organizations, particularly when it comes to databases. As developers will tell you this can be frustrating, especially if your organization is one of many that separates the DBAs and developers into different departments and you feel you have to jump through hoops to get any changes made to a database. I believe this book goes a long ways to break this rigid style of working.

I am a firm believer that data and the structure around it, in other words databases, are an organization's most valuable asset today! An organization could go out of business overnight if their data was lost, for some odd reason.

Having a great database design can be achieved best when it is refactored as necessary. This book is about the only book out there that covers this topic and it does a great job at it!

I'm glad this book is a hardcover (as it should be), because this one will be on my book shelf, readily accessible to me, for a long time to come. In short, I highly recommend this book to DBAs, developers and more!

It's been almost 7 years since Fowler's Refactoring book, and now the database community has finally caught up with the rest of us. This book shows how to refactor a relational database schema, working you through the detailed process steps for doing so and providing the source code for implementing more database refactorings than I would have thought existed.

The first five chapters describe how to go about database refactoring. Chapter 1 overviews the idea that you can evolve your database schema in small steps, a radical departure for many traditional DBAs. It also overviews the need for supporting techniques such as agile data modeling, database regression testing, and version control of your data models and scripts. I would have liked to see more coverage of these topics, but at least the modeling material is covered in Ambler's Agile Modeling book and there are some great SCM books out there.

Chapters 2 and 3 walk through the process of implementing a database refactoring, first through the simple situation where there is only a handful of applications accessing the database. I guess this sort of thing happens in smaller companies, but most of the time you really have to worry about scores of applications accessing your database which is a much harder situation. This is actually the focus of Chapter 3 and of the presented solutions in Chapters 6 through 11 which provide reference implementations for all of the database refactorings. This approach belies the true strength of the book: it reflects actual experience in large organizations, not just the theoretical pie in the sky stuff you see from other authors.

Chapter 4 focuses on deploying database refactorings in production, providing detailed instructions for how to roll refactorings between various sandboxes. It importantly describes how to merge the refactorings of several teams together. If you have 100 applications accessing a shared database, then potentially you need to manage the refactorings coming from 100 different development teams. Of course it would never be that bad, but even merging refactorings from 10 teams would be tough. This might be where the technique falls apart because many companies likely don't have data managers who are skilled enough to do this sort of thing efficiently enough to keep up with agile developers. We need new tools, so hopefully companies like Oracle will build something for us.

Chapter 5 describes a collection of best practices and challenges pertaining to refactoring databases. The authors share their experiences as well as identify potential issues, such as a lack of tooling and agile expertise within the data community, that could slow adoption of this technique. My guess is that the smarter teams within companies will start doing this stuff right away, for the most part it's pretty easy technically, but that bigger companies will struggle to change as they always do.

Chapters 6+ are reference descriptions for the individual refactorings. Each one is described using a UML data model, which is a little strange at first although once you get used to it you can see how it's a much better notation than Crow's feet, a detailed text description and source code. The source code examples are detailed, I guess the authors want to be thorough and provide a complete solution so that there's no question how to implement each refactoring. The application examples are written in Java or Hibernate, but they're simple enough that you could see how to implement them in C#, C++, Ruby, or even VB. The database code is Oracle, once again it's pretty straightforward so you can easily see how it would work in other DBs like Sybase or MySQL.

All in all, if you're a DBA or agile programmer you need to seriously think about buying this book.

Ambler and Sadalage describe a potentially very useful idea. That you can migrate the idea of code refactoring to databases. This appears to be a relatively new activity. Due in no small part to the database developer community having been separate from programmers using general purpose languages like C++ or Java. If you are in a large company with both types of people, you have probably noticed that the skill sets and interactions between them can be and indeed often are limited.

The authors quite reasonably suggest that this caused database developers to miss out on various changes in the programming field since the 90s. Notably in the rise of object oriented programming. Rather different from the dominant SQL relational approaches. The OO mindset in turn led to the rise of code refactoring.

In response, this book suggests ways that databases might be refactored. The pragmatic aim is to easily accomodate changing user requirements, by being able to perform relatively small, evolutionary changes. Readers should be warned that applying the lessons of this book may be harder than standard code refactoring. A database might be tightly coupled, both internally and to numerous downstream applications. Nonetheless, many possible refactorings are suggested. Each being easy to understand and perhaps even to implement, in your database.

The authors have also tried to suggest refactorings that can be applied across any specific SQL database. Of course, different database vendors means different optimisations, usually for speed of handling queries. So possibly the book's refactorings are best suited for handling changing requirements and code maintenance. But for raw performance improvements, you may have to consult specific texts for your database.

This book does an excellent job of pointing you in the right direction for almost anything you can think of database related.

One of the best things about it is it uses UML syntax for DB designs, which really helps out if you are using a tool like SPARX EA.

He breaks the refactorings down into categories: Structural, Data Quality, Referential Integrity, Architectural, Method,
Non-RefactoringTransformation. They dedicate a full chapter to each category.

Chapter 3 provides a great process to implement to accomplish Database Refactoring.

They provide an Appendix for UML Data Modeling Notation. So you have a reference right there in the book.

The author does a great job of thoroughly explaining everything and has a great writing style.

Here is the Table of Contents to give you an idea of what is in the book.

Preface
Why Evolutionary Database Development?
Agility in a Nutshell
How to Read This Book
About the Cover
Acknowledgments
Chapter 1. Evolutionary Database Development
Section 1.1. Database Refactoring
Section 1.2. Evolutionary Data Modeling
Section 1.3. Database Regression Testing
Section 1.4. Configuration Management of Database Artifacts
Section 1.5. Developer Sandboxes
Section 1.6. Impediments to Evolutionary Database Development Techniques
Section 1.7. What You Have Learned
Chapter 2. Database Refactoring
Section 2.1. Code Refactoring
Section 2.2. Database Refactoring
Section 2.3. Categories of Database Refactorings
Section 2.4. Database Smells
Section 2.5. How Database Refactoring Fits In
Section 2.6. Making It Easier to Refactor Your Database Schema
Section 2.7. What You Have Learned
Chapter 3. The Process of Database Refactoring
Section 3.1. Verify That a Database Refactoring Is Appropriate
Section 3.2. Choose the Most Appropriate Database Refactoring
Section 3.3. Deprecate the Original Database Schema
Section 3.4. Test Before, During, and After
Section 3.5. Modify the Database Schema
Section 3.6. Migrate the Source Data
Section 3.7. Refactor External Access Program(s)
Section 3.8. Run Your Regression Tests
Section 3.9. Version Control Your Work
Section 3.10. Announce the Refactoring
Section 3.11. What You Have Learned
Chapter 4. Deploying into Production
Section 4.1. Effectively Deploying Between Sandboxes
Section 4.2. Applying Bundles of Database Refactorings
Section 4.3. Scheduling Deployment Windows
Section 4.4. Deploying Your System
Section 4.5. Removing Deprecated Schema
Section 4.6. What You Have Learned
Chapter 5. Database Refactoring Strategies
Section 5.1. Smaller Changes Are Easier to Apply
Section 5.2. Uniquely Identify Individual Refactorings
Section 5.3. Implement a Large Change by Many Small Ones
Section 5.4. Have a Database Configuration Table
Section 5.5. Prefer Triggers over Views or Batch Synchronization
Section 5.6. Choose a Sufficient Transition Period
Section 5.7. Simplify Your Database Change Control Board (CCB) Strategy
Section 5.8. Simplify Negotiations with Other Teams
Section 5.9. Encapsulate Database Access
Section 5.10. Be Able to Easily Set Up a Database Environment
Section 5.11. Do Not Duplicate SQL
Section 5.12. Put Database Assets Under Change Control
Section 5.13. Beware of Politics
Section 5.14. What You Have Learned
Online Resources
Chapter 6. Structural Refactorings
Common Issues When Implementing 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
Chapter 7. Data Quality Refactorings
Common Issues When Implementing Data Quality Refactorings
Add Lookup Table
Apply Standard Codes
Apply Standard Type
Consolidate Key Strategy
Drop Column Constraint
Drop Default Value
Drop Non-Nullable
Introduce Column Constraint
Introduce Common Format
Introduce Default Value
Make Column Non-Nullable
Move Data
Replace Type Code With Property Flags
Chapter 8. Referential Integrity Refactorings
Add Foreign Key Constraint
Add Trigger For Calculated Column
Access Program Update Mechanics
Drop Foreign Key Constraint
Introduce Cascading Delete
Introduce Hard Delete
Introduce Soft Delete
Introduce Trigger For History
Chapter 9. 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
Chapter 10. Method Refactorings
Section 10.1. Interface Changing Refactorings
Section 10.2. Internal Refactorings
Chapter 11. Transformations
Insert Data
Introduce New Column
Introduce New Table
Introduce View
Update Data
The UML Data Modeling Notation
Glossary
References and Recommended Reading
List of Refactorings and Transformations
Index