Silly Arguments: Schema vs Delta Database Change Control

As we look at arguments we care too much about, one of those would be “should we use a schema (or model) based solution database change control, or should we store off deltas?”

First off, source control is useful, and not just for our application code. Storing the database somewhere in source control along with our application code is essential to establishing a quality build process. If you have a database, that means you likely have database scripts (of some sort). Not having those scripts under database change control is a big mistake.

Before jumping into schema vs delta, I think I should describe the differences a little. With a schema-based solution we store the schema (in some form).

With a delta-based solution, we store the delta to a schema.

In both cases, we have something many organizations don’t have: a controlled way to stand up a new QA database. We also have a controlled way to deliver production database deployments. The important thing is we are storing our database changes in source control.

I prefer a delta-based solution to this problem. I think it tends to be closer to how I develop software. When making a change to a table, I write a SQL script to make the modifications to the table. Saving those scripts off in source control is very easy to do.

Some people prefer to just save off the current schema. This option does have a big advantage in that source control will show you what your current schema looks like. But schema-based versions do have an issue: if you ever need to write a script that works with columns that already have data, you can end up in an awkward scenario. If you must move data between columns, or delete a column, schema-based solutions often prove difficult.

But regardless if you are using schema-based or delta-based, as long as you are saving your database changes into source control you are doing the right thing.