How should I update non-maintained database tables between production and development?

Question

I'm working on a Django web application (with a mySQL back-end) that uses non-maintained tables(tables not modified by the web app). However, I have two copies of the data tables (one for production and one for development).

A problem occurs when I need to manually modify the non-maintained data in the development version. Because there are two separate tables, I need to manually update the corresponding data in the production version.

Is there a recommended form of version control for the non-maintained tables? I was thinking of using SQLite for the non-maintained data tables and letting Git track their files.

To clarify, the production and development versions each have their own separate databases.

The production version's database has 1 copy of the non-maintained data tables. The development version's database has 1 copy of the non-maintained data tables.

The non-maintained data consists of standardized data (ie county FIPS, city lat/lngs, etc.)

Answer 1

A problem occurs when I need to manually modify the non-maintained data in the development version.

Then don't.

Instead, create an SQL script which does. Reapply that script at an appropriate time during deployment of the next release of your application.

Is there a recommended form of version control for the non-maintained tables

Well, I cannot tell you what others would recommend, but I would suggest to use your standard version control system for the script.

Some additional recommendations:

• make sure the script execution behaves idempotent (that means, accidentally executing it twice does not matter, won't create duplicate data or make other parts of the deployment fail). This may be already the case because of existing constraints, or you need explicitly maintain some log table where script executions are recorded in a traceable way

• if the application needs to be updated in conjunction with the data update, make sure the new release of the application works with the old, non-updated data in a graceful way. There could be a time lag between an update of both, be prepared for it.

Answer 2

Why do you have both tables in the same database?

I'd expect to see one table in each of Development and Production, with each holding the values relevant to that environment. Having both in the same database at the same time is just asking for trouble, say, when you accidentally change the wrong one!

Because there are two separate tables, I need to manually update the corresponding data in the production version.

That suggests that you want to same data in both tables.
That makes me question why you need two of them at all.

Is there a recommended form of version control for the non-maintained tables? I was thinking of using SQLite for the non-maintained data tables and letting Git track their files.

Again, this sounds to me like this table contains configuration settings that are specific to each Environment. I'd go with one table in each database and use [version controlled] SQL scripts to apply changes to either of them, with the relevant Change Controls around doing so.