Today I had a request from a customer with a database used for What If scenarios to allow him to do either do updates from the main tables consistently or to boot all other users out of their front ends so that he can do an update when one is required.

The system is looking at forecasted deliveries, purchase orders, and shipments and consolidating them all to look at how many parts are being used and being forecasted to be used in a future time frame.

The update rebuilds tables based on the live data to create a master part list since this when the live data changes, this might invalidate some records in the forecasting system.

I spoke a bit with CoPilot to discuss my thoughts and to get its thoughts on the best course of action for my use case.

In this instance, I am leaning toward the following general solution:

1. Don’t require exclusive access to the tables. In order to do this, create a brand new back end with new and updated tables on each update.
2. Create an application table to store the name of the latest database to use. Any prior users would be using the old table until the update routine finishes.
3. When the update routine finishes, update the application table to tell the application to start using the new back end.
4. I will want to have a delete routine to attempt to delete old databases when they are no longer in use.
5. I will want to make sure the front end database will be able to gracefully switch to the new db once it is detected.

This approach should allow the update to commence whenever the admin desires and his database will immediately start using the new tables, while leaving the existing users sessions and data intact. Once they are notified of the new update, their front end can alert them and switch them over to it so they see the latest data.