Switching Between Sets of Database Tables On The Fly

Ever need to tell your ColdFusion app to run select queries against a different set of tables while you do something to the original set (like perform a batch update)?

I did. Here's how I did it.

First, I gave each table in each set a two-part name, two words separated by an underscore. The first part of the name was descriptive of the data in the table ("chapters","sections", etc.) while the second part of the name identified the data set the table belonged to ("live", "archive", whatever). So the "chapters_live" and the "chapters_archive" tables were exactly the same in structure.

Then I created an application variable called "application.tableSuffix" and set its initial value to "live".

(Can you see where this is leading?)

Finally, I changed my select queries to use table names comprised of the first, data-descriptive part of the table name, the underscore, and the value of application.tableSuffix:

<cfquery name="showChapters" datasource="ds">
   select chapterTitle, chapterIntro
   from chapters_#application.tableSuffix#
   order by chapterOrder ASC
</cfquery>

Once that was done, I could change the table set my select queries ran against at any time by simply changing the value of application.tableSuffix. Two things worth noting:

• This technique only makes sense if only one set of tables can be updated by the user: if users can make changes to either set, you'll have to figure out how to resolve the differences between the data in each set.

• If the switch could potentially be in place for awhile, you might want to store the current tableSuffix value in a database record or a file and then retrieve that data in the onApplicationStart method of your Application.cfc file so that the current value is preserved even if the application scope expires.