I've been floundering about how to handle the record ID's on a table that gets it's records from two sources. I hope someone can offer an opinion...
I'm moving payment records from a live table to a snapshot table. I want to preserve the field structure and auto generated unique record ID of records from the live table when they are inserted into the snapshot table, so the snapshot table is a copy of the live except that auto increment for the record ID is disabled. While payments are grouped and ID'd primarily by work order ID, the original record ID allows live and snapshot payment reports to show payments by the same record ID.
So far so good, except now, the client wants to be able to add new records directly into the snapshot table. This creates a problem in that a new payment record that is directly added to the snapshot table could have an ID that collides with an ID from a record that is subsequently inserted into the payment snapshot table from the live table.
To solve the problem, i have a few choices:
1. Leave the snapshot table structure as is and generate unique record ID's via UUID() or UUDI_Short() for payments added directly into the snapshot table.
Downside: reduces an access performance and ID's from live table will look wildly different then those Id's that are directly added.
2. Restore auto increment to the record ID making it a primary field in the snapshot table (a duplicate of the live table). All records inserted into the snapshot table (whether directly or from the live table) will get a new system generated record ID avoiding any collisions.
Downside: original live table payment record ID's are lost. However, the ID that is primarily used for grouping and IDing the payment records to work orders, the work order ID, is still there.
3. Create a composite key for the snapshot table made up of a new field that stores a character ("L" for Live or "S" Snapshot) and the old record ID field. The record ID field of the composite key would be set to auto increment. When inserting a record from the live table, the original record ID is inserted along with an "L" in the new field. When a new payment record in added, a new record ID is generated along with an "S" in the new field. This method would eliminate any collisions and preserve the live record ID.
Downside: may not be possible.
I would appreciate your opinion on the above approaches.