Developing database-backed software, often one finds it convenient to store stuff in the database which one might traditionally express in code, but which is useful to store in the database (so that it can be changed without requiring a reload of the application).

For instance, one might have processes which send automated email using a template; it makes sense to store this template in the database, so that the text can be tweaked by the users without having to change the code/redeploy/etc.

However, these templates are "important"- they are required for the correct operation of the system, and for instance, if they become malformed or missing, the underlying functionality would probably stop working. As they live now in the database, they don't outside version control- you might add audit tables to have change history about this information, but revisions/etc. are separate and independent from your main code's revisions.

How do you handle this kind of stuff? Not putting this kind of stuff in the database and make changes go through development/change control/deployment cycles? Or something else?

5 Answers
5

If I got you right, you have data for which may be edited by your users (at least, to some degree), but this data is "required for the correct operation of the system" and "if it becomes malformed or missing, the underlying functionality would probably stop working". There are some things you can do here:

forbid your end users to manipulate that data. You can store those data in the database, but your program does not offer any functionality to change it. The only one who is allowed to change that data is, for example, an update installer provided by you when a new release of your software gets deployed. Or, you allow your end user copying of that data and let them manipulate the copy, but never the original thing

allow only some special users (admins) to change that data in the database (but beware, admins can make errors, too)

add a functionality in your application (or only for admins) to restore that data to its original state provided it becomes malformed or missing

provide a functionality to check if the data is malformed or missing; run this check after someone changes the data, and (more important): run it whenever your program tries to use that data

separate the parts of the data which can be safely changed by the end user clearly from the parts which cannot (and don't allow users to change the latter)

try to make your updates "intelligent": when you deliver a new release, including an update installer for your database, th installer should expect data manipulation in, for example, your template table, and act accordingly

It should be obvious that each of these suggestions can be combined with each other.

Most thorough answer. However, in some areas putting the data in the database still has some drawbacks wrt. to putting the data in the code. It also has advantages of course. I don't think there's a clear winner here...
–
alexAug 6 '12 at 18:51

@alex: if you want the data to be changed by your users or admins at run-time, it has to be stored somewhere outside of your code, and when you use a database, that is typically the place where you store it. If your applications offers a "restoring" functionality, however, you need your default version of the data additionally in your code. And SQL scripts are code, too. On the other hand: for data which should never been changed by no means by a user, there is normally no need to be stored outside of the code.
–
Doc BrownAug 6 '12 at 18:59

Many systems can be broken just as easily with bad configuration settings as with bad source/logic: Config is source (or at least just as important), so configuration settings should be stored under version control just like source/logic.

Common automation mechanisms can be used to synchronise production systems with configuration and source changes.

For example:

Keep code & config that is currently in production in a particular area of the repository. (Call it "prod", and place restrictions on who can commit to that location).

To change configuration, make the changes and commit the modified files to prod. Your development process will probably dictate some level of testing before the changes are actually applied to prod.

Once this happens, let your continuous integration system pull the files back out of prod, and (after some automated checks have been performed) push the changed configuration out to the actual production system. A database may well be the mechanism that is used to change configuration without restarts.

The important thing is that automation is used to ensure that the "prod" branch in the repository holds the code and the configuration that is really in production at any one time. (That way, if anything goes wrong, you will not be led astray by out-of-date or inconsistent config and/or source files).

For your particular example, I would be tempted to carefully define the extent to which customers can customize or otherwise modify the functionality of your system. People are fiendishly clever at finding novel ways to stretch a technology beyond the intentions of its creator.

We've actually brought important configuration tables under source control. We're using Red Gate Data Compare which works great. But if it's too expensive, it's easy to write a custom tool that exports configuration tables to a text files. Those text files can then be versioned with source control.

An hourly job checks if there are changes in the database that are not in source control, so we are aware if anything changes outside the regular process.

It is better to keep templates in DB for the reason you listed in your question and also because you might have a huge number of templates which needn't be loaded at once.

An easy way to tie templates with app versions is to tag them. The app should have a static field indicating which version of the template is up-to-date. That field could be a
number, string, date, whatever (number is the best, it is easier to search with in DB).
The Template table in the DB should also have a column for the tag of the same type. So when requesting for a template the app could add another condition specifying the tag.

There could also be a default template (the newest one, for example), if the new version of the app doesn't need an updated template. Or the new version could have the same tag as the previous one.