Even if you had perfect database design from the start, it is likely that requirements changed, and you have to change database schema accordingly. As there is legacy code, there can be legacy data, with schema designed for use cases which are no longer actual.
It is usually harder to fix data design than to fix code design, but it is doable. And as with code, you can apply refactoring techniques: improving the design without change in behavior.
A book “Refactoring Databases: Evolutionary Database Design” describes basics of database refactoring and lists different kinds of changes to relational databases. It starts from explanation why refactoring is a good thing, why it is important to make small incremental changes, and which organizational obstacles you can get on the way of implementing these techniques. You can imagine how hard it can be to make database changes if DBA and application developers are in different teams, and any database change requires coordination between these teams, sometimes using strict change management process. Basically, agile methodologies with their focus on cross-functional teams and short development cycles can help here.
Continue reading →