Onward with SmileAndDial CRM, and their quest to remove phone numbers as a fundamental constraint! Part 2 introduced the concept of TheeSeeShipping, a low risk, iterative way to make large sweeping changes.
At the end of part 2, the database looked like this:
New Id columns have been added, but they are unused and null.
Now SmileAndDial’s developers can TheeSeeShip small, incremental, low risk changes to production. These changes are invisible to users and can be worked in with day-to-day work.
TheeSeeShipping works like a ratchet; once a change is complete, it stays complete. Whether the work is done quickly or slowly, change is inexorable and one way.
The Second Series of Releases – Propagating ContactId
The second series of releases is to insert the new Contact.Id value into other tables’ ContactId value when rows are inserted.
This requires lots of small changes – getting the new Id after inserting into Contact and adding it to the insert statement in other tables. But, because the fields are nullable, there are no foreign keys, and no query logic; these changes can be done incrementally.
Fix one or two inserts every release. These are small, easily testable changes. Grab the Id, add it to the insert, confirm that the row was written with the correct ContractId.
TheeSeeShip it in low risk, two line changes until all new rows have a ContactId.
The next step in this series is to backfill the existing data. The Contact table has an Id for all rows. Run an update query for each table to backfill the data.
Guess what, since the data still isn’t used in the system this is also a low risk query.
Now every row in the database has a value for ContactId, and every new row will get a value on insert. The time has come to remove the nullability and add in the foreign keys.
The Third Series of Releases – Updating Selects
With ContactId propagated to every table in the database, the time has come to update the Select queries.
Instead of selecting from every table by phone number, SmileAndDial needs to run off of ContactId, or join Contact to use Phone
Each of these changes can be made incrementally in a separate release. They are small, easy to test, and independent of each other. TheeSeeShip it; the data model is different, but to customers it is still SmileAndDial.
After any bugs or missing ContactIds get fixed, it is time to remove the foreign key constraint on the Phone field. Note that this can be done at any time that is convenient to the DB team. There’s no rush and no impact if a bug with ContactId does turn up.
The Fourth Series of Releases – Removing the Fundamental Constraint
Everything in the first three series of releases was incremental and non-breaking. Each change was minimal and non-breaking.
Even at the end of phase 3, the discovery of an unmodified table would have no impact on the system. The table and code would continue to work as it always had. Any mistakes were easy to roll back.
Everything had changed, and everything was still the same.
In the fourth series of iterations, Email will be removed as a fundamental constraint in the system. This, finally, is a breaking change that is not trivial to undo.
But, it is also a change that is easy to do in an incremental, low risk way.
Incrementally remove the Phone column from all tables except for Contact. At this point there are no foreign relations or Select statements that use Phone.
Finally, when Phone is gone everywhere else, make Contact.Phone nullable. Update the service and UI logic to allow the creation of a contact without a phone number.
Phone Is No Longer Fundamental
There are a lot of incremental changes and releases in the four series. The key is that each change was very small and very low risk. After each release customers had a fully functional, up to date, version of SmileAndDial.
Unreleased work in progress was low and work could have halted for months at a time without any of the completed work suffering from code rot.
TheeSeeShipping changed the fundamental nature of SmileAndDial without ever taking SmileAndDial out of the hands of its customers.
