I'm developing a standalone application that accesses a common database on a server. It will be used by a few dozen people in my organization.
I know that I will need to update the software and the design of the database. This creates the possibility that someone will perform a query on the database using old software.
I already have a system in place that will catch if the user tries to start up an out-of-date version of the application. However, this does not guard against someone who keeps the application up most of the time.
My idea was to put a version entry in the database (in tblVersion or something like that) and check it each time any record in any table in the DB (except tblVersion) is added, updated, or deleted, but not merely read.
This way I could catch things that (I think) could corrupt the DB even if the user has an out-of-date version of the SW: check the version in the DB against what's in the code, and disallow the add, update, or delete operation if there's a mismatch. At the same time, I wouldn't be adding the overhead of a lot of checks to tblVersion for DB reads.
My questions: Is this sound? Is there a better way to go about it? If so, what?
Thanks!
The use case of a person having the app open for so long that a new, incompatible rollout of the app occurs and the database schema (or interpretation) is modified in a structurally compatible, but semantically incompatible way seems pretty narrow.
Having a schemaInfo table with and having two tuples, one with the current schema version and another with the last compatible version is going to catch the vast majority of cases while not overly complicating your application.
Checking on every write would be a horrible mess, if you're really concerned about the scenario, have a timer go off every hour or two that checks the table version and pops up a dialog warning the user to get a new version.
Even better you could simply bounce the DB forcing all existing sessions to disconnect once you've finished a schema change.