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?
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.
If I got you right, you have data 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:
It should be obvious that each of these suggestions can be combined with each other.
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.
Create one table ::: Templet Table ( Templet ,ModifiedDate , Version ,UserName , Published (Y/N) ) .
Published : Read the templet into app only when published=Y ,Version: To maintain the version of the document .
And create on admin UI to manager the templets .Maintain your admin code in such a way that only one published templet should be there for one templet .
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.
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.