Split table — A Database Refactoring Technique
Recently I came across a situation where I had to split a table into multiple. Our product supports renewing billing cycles and, we assumed there would be only one payment per billing cycle. Later we realised that there could be partial payments which required accepting multiple payments for a billing cycle. So we decided to split the table into two as shown below:
We also needed to make changes to the UI to support multiple payments and also required to build few other supporting features. The change will take us longer to complete and needless to say we wanted to do Continuous Delivery rather than creating a branch and implementing in isolation and merge it before deploying.
In one of my earlier posts — Continuous Delivery for Database — I mentioned about using the Expand-Contract pattern for Split Column refactoring. Let’s see how the same can be applied for Split Table refactoring.
In Expand Contract Pattern, aka the Parallel Change pattern, both the old and the new implementation co-exist during the transition period. And we will delete the old one once the application migrates entirely to the new one.
The transition period can be due to the long refactoring period or when there are downstream applications, i.e. the applications that depend on the current structure, and it takes time to change those.
During the transition period, the data need to be saved in both the schema. I used Active Record Callbacks for this. Another way to implement this is using the database triggers.
The above is an example of Preparatory Refactoring as mentioned by Martin Fowler. I used the above example in my previous post about the same topic — Refactoring Workflows. And this is the way to implement emergent design — for the code as well for the database because change is the only constant.
The book Refactoring Databases talks about how each refactoring [as defined by Martin Fowler] can be applied to databases. Below is the catalogue of the same.
