I'm working on a solution that's in the field already (on 3 continents), with hundreds of units (each one being it's own server or a small shared network (no servers).
I'm fully expecting that within a year we'll need to migrate versions to an upgraded version of the solution, and am trying to make sure we're headed in the correct direction.
I'm highly concerned about the serial# issue (all tables use k_ID fields) - but unfortunately all were developed using a simple serial#, no UUIDs... so this concerns me WRT potentially importing fields and accidentally overlapping key ID#s during the process.
Surely somebody has been thru this and has a good method of solving this issue? I'm really searching for an automated method to accomplish this, such that upgrades can be done remotely (we've got folks accessible only by satphone, and i cant imagine attempting an upgrade process over Teamviewer by sat connection)...
This is slightly complicated by the fact that the previous version has the serial# (key field) operating as a visual identifier (think: "Patient# 12654"), and as hundreds of users are used to this identifier field, it's not exactly easy for us to remove that and convert it into a UUID for key field useage.
I intend to move key field operation into a hidden field (based on UUID), but still need the visible '"client ID#" field to remain and to continue to serialize for folks in the field.
My initial thought was to simply create a serial# key field calculation for the new version, which was inward looking (a field that was related by cartesian to itself), with an auto-create of a "Max(self)+1" calculation to continue serial creation. However, I ran this on several in-house platforms and found it to significantly bog down the server on tables with >100,000 records.
Is there a collective recommendation for such an animal?