Database Refactoring

I'm taking ownership of a database, so I've been reading about database development best practices. Database development is unique from other kinds of development for a number of reasons. Most crucially, you can never start from scratch with a database, because it's a living, breathing thing--you can't simply recompile and redeploy. Second, you have to design your database so that it can be set up with representative data on developers' workstations, in a development environment, and a testing environment. Third, you have to make sure that any scripts can be deployed on any environment (it usually helps to have some way of knowing what 'version' a database is on). And you may have to do all this while being unsure of all the applications that access a database, or the manner in which they do it.

These challenges led me to read Scott Ambler and Pramod Sadalage's book on database refactoring (2006). "Refactoring" is a term popularized by Martin Fowler's eponymous book (1999). He defines it as a "disciplined technique for restructuring an existing body of code, altering its internal structure without changing its external behavior." It is important to have a concept like refactoring, because development is usually focused on adding functionality only. When things change quickly, who has time to refactor?

There is some anecdotal evidence that spending time refactoring will decrease the amount of time coding later, but there's no rule for how much time you should spend refactoring. However, if you can't do any of the things mentioned above (limit access to your database, know what version it is, deploy to multiple environments), you must refactor. The hoops you'll have to jump through just to do normal development are far costlier than the effort needed to refactor. Ambler and Pradalage also suggest that it might be time to refactor if you notice any of the following "database smells": multi-purpose columns, multi-purpose tables, redundant data, tables with many columns, tables with many rows, "smart" columns, and fear of change.

Defining refactoring in the way Fowler does is also important, because it simplifies testing by separating the development of added functionality from development that should preserve existing functionality. Ambler and Pradalage embrace a Test-Driven Development approach, where you test, deploy, and test again. If you make testing central to your development process, you can be sure that you're not breaking anything during refactoring.



Though refactoring seems necessary only in (hopefully) rare circumstances, the steps that constitute it should bleed into normal development. For example, it's always a good idea to develop in small, easily testable pieces, rather than making many large changes all at once. An iterative process also allows for feedback from users. In short, refactoring should help you to embrace AGILE principles in database development, and it should make further development faster and easier. Even if you can't make your database development completely AGILE, these principles should help you evaluate bottlenecks in your process and suggest means of addressing them.

Links:
-An article that contains the main sections of the book

Software Gardening

The Pragmatic Programmer has a lot of nuggets. Here's one:

The most common metaphor for software development is building construction. Using construction as the guiding metaphor implies these steps:

1. An architect draws up blueprints.
2. Contractors dig the foundation, build the superstructure, wire and plumb, and apply finishing touches.
3. The tenants move in and live happily ever after, calling building maintenance to fix any problems.

Well, software doesn't quite work that way. Rather than construction, software is more like gardening--it is more organic than concrete. You plan many things in a garden according to an initial plan and conditions. Some thrive, others are destined to end up as compost. You may move plantings relative to each other to take advantage of the interplay of light and shadow, wind and rain. Overgrown plants get split or pruned, and colors that clash may get moved to more aesthetically pleasing locations. You pull weeds, and you fertilize plantings that are in need of some extra help. You constantly monitor the health of the garden, and make adjustments (to the soil, the plants, they layout) as needed.

I'm trying to think this metaphor through, as, until now, I had not questioned the metaphor of architecture. Intuitively, I have a sense for what Hunt and Thomas mean. After all, these guys started the Agile Alliance, which takes the 'waterfall' or linear model of software development as its main target. The four main principles of the Agile Manifesto are:

1. Individuals and interactions over processes and tools
2. Working software over comprehensive documentation
3. Customer collaboration over contract negotiation
4. Responding to change over following a plan

I have done some organic gardening, so I have a little experience to draw upon to understand what Hunt and Thomas are getting at.

The first thing I learned gardening is that you cannot start from scratch. No matter how much pesticide you spray or soil you import, there are soil, drainange, water, sunlight, insect, animal, and other conditions that shape what will grow and what won't. The earth has been here a lot longer than your garden has. In the software world, you don't begin at the beginning either. You have a team's knowledge and relationships, IT infrastructure, available languages and platforms, customer expectations, and the market of existing competitors and third party vendors, to name a few. And, of course, the majority of work as a software developer is not development but refactoring existing code.

The second thing I learned is that things rarely work out the way you planned and that you have to adjust to these events. One year, a groundhog ate almost all our butternut squash. Another year there was a tomato blight. We built an electrified fence to cut our squash losses, and we made sure to not plant tomatoes in the same place next year. In software, you have to do what you can to contain unexpected events. This happens at a macro and a micro level. For instance, on a micro level, code should be robust enough to handle exceptional situations. At a macro level, it should be flexible enough to handle change. For instance, in order to best adapt to increasing demand, your system should designed to scale. If you might have 10 million new customers in a month, you must make sure that your database architecture can adapt to that demand.

The last main thing I learned is that to be a good gardener, you have to be able to learn. No one is born with a green thumb, but some people learn more quickly than others what works and what doesn't. It's a lot easier to start gardening if you know some people who already garden. And no matter how much reading you do, there is always more to learn, because there are always new things you have to deal with. The same is true for software development. Many programmers are 'book-learned' and can't apply their knowledge, or they are narrowly knowledgeable about a handful of languages or platforms. It amazes me when I hear people describe themselves as either back-end and front-end programmers, because a good programmer must be both. If you follow Hunt and Thomas's advice, you'll learn a language a year!

Of course, gardening doesn't sound quite as cool as architecture, but good architecture follows many of the same practices outlined here. For example, modernist architects are accused of ignoring site specificity and simply plopping down skyscrapers in the middle of thriving urban ecosystems. If you don't learn from previous projects, you'll get a bunch of haunted shells like those that make up Dubai's skyline. Buildings are much less malleable than code, but that is all the more reason not to use them as a model.

Links
-An interview with Andy Hunt and David Thomas about software gardening