Can Database Refactoring be Agile?
I was fortunate enough to partially attend the Much Ado About Agile Conference held recently in Vancouver, and was immediately drawn to the session “Database Development in Agile World” led by Marc Munro. This was a very timely session as we have recently begun a project that involves refactoring a legacy database and developing a new application to replace the legacy system. A data migration from the legacy system to the new system would also of course be necessary. We had only been working on the new application for a week or so but had already been running into problems and I was looking for some advice. We do things in an agile way here at 2Paths, and we were struggling with how to coordinate the database refactor / data migration with the creation of a new application.
After some discussion of whether we should start development on the database or application first, we decided to tackle the database first. We had started out by re-designing the database to allow for some sorely-needed normalization of some relationships. We came up with a new normalized schema through a mixture of requirements gathering with the client and reverse-engineering the legacy database. We focussed on areas where we could generalize and abstract out relationships and left the details for last. With our new schema, we then worked on migrating the legacy data into the new schema to ensure everything was accommodated.
Using our new schema we began application development with Grails. We began having difficulties where the application was changing rapidly, necessitating corresponding changes in the schema and data migration script. We were having a disconnect between the schema changes and the application changes. It was at this point that I attended Marc’s session.
The main point that he drove home which we already knew too well was that Database refactoring is expensive. Databases have no source code, databases resist change, databases need to be coordinated with corresponding applications, and teams usually have many databases to coordinate (ie: dev, staging, production). Marc stressed the first point of there being no source code as one of the major problems. Database creation and maintenance are usually done as ‘patch scripts’.
Enter LiquiBase. Marc neglected to mention this very handy library that we decided to try out with our new project. LiquiBase is DBMS-agnostic and allows us to manage database change much more cleanly than ever before. I haven’t fully explored all of the LiquiBase functionality yet but so far am very pleased with what I have explored. There is a LiquiBase Grails plugin and also a LiquiBase IntelliJ Idea plugin that we’ve been using.
Back to Marc’s presentation. Because we’re already convinced that Database refactoring is very expensive, he made a case to do Big Picture database modeling up-front, most importantly capturing all the areas where generalizations and abstractions can be made. It is much more expensive to refactor something specific into something general than to refactor something general into something specific. Adding objects rarely if ever affects existing functionality, but removing them usually does.
Start by modeling the entities and relationships only. Don’t worry about the attributes or details – these can be fleshed out during implementation.
Marc had some Dogmas to impart:
- Use subtypes: they’re great for recording extra knowledge about a logical model
- Hate NULLs: allow no optional relationships. This usually requires using a linked entity which may seem uglier but makes a whole lot more sense
- Allow no implied participants in transactions
And then there was Pragma:
- Don’t invent when you can copy
- Use standard models. He highly recommended Len Silverston’s Data Model Resource Books which we also refer to here at 2Paths
- Reuse models when you can
So far in the presentation, it didn’t seem like we were in an “Agile” session, but finally Marc roped us back into the subject at hand. Just because we’ve done up front design doesn’t mean we have to implement everything at once. Using the up front design we can do iterative development. We don’t need to have the up front model be 100% correct – we can do iterative refinement to it as the project progresses.
For an agile implementation, revisit and refine the model at the start of each iteration. Make explicit documented decisions on each of the design decisions that are made so that down the road it’s known why certain decisions were made.
In summary, Marc reiterated that because databases are so expensive to refactor, it’s important to get as much right the first go around as possible. Getting it right first will make agile development and subsequent refactors much easier. Design for change by generalizing and abstracting relationships.
Relating Marc’s advice back to our project, I realized that we hadn’t done too badly with our process after all. We had made generalizations and abstractions where we could, getting the Big Picture logical design done up front. We then began application development based only on the stories included in the current iteration. The problems we were having in syncing the application to the database were mainly mapping to physical design issues, and not logical design issues.
In hindsight, it may have been easier if we took the logical model that we had designed and implemented this in Grails first and seen what schema Grails produced from that model. We could then have tweaked this schema and associated mappings to be the physical implementation we wanted, instead of producing the physical model in the database first.
When we had started the process of our up front design we had had many discussions about whether this was the right thing to do, and if it was really agile. It was good to have confirmation that others have thought of this too and that it IS okay to do Big Picture up front design. This will hopefully save us some pain and suffering in the future. Hopefully we’ve made our future database refactoring less expensive.
Tags: agile, database, much ado about agile, refactoring
