nelsonm - 12:51 am on Oct 25, 2012 (gmt 0)

Yes, I'm moving all live work order and ancillary records to the snapshot tables that are not part of lookup tables. Lookup table records are just copied.

A live work order table record points to other supporting live tables such as the customer, items and payment tables. When the work order is closed, those live table records are inserted into the snapshot tables (which are a duplicate of the live tables except that the key fields have auto increment disabled) then the live work order, item and payment records are deleted from the live tables. Since the live customer table is a lookup table, the customer record is not deleted from the live table after a copy of the record is inserted into the customer snapshot table. So in essence, all live work order related records are copied to the snapshot tables then deleted from the live tables - except live lookup table records.

So, in order to do what you suggest in your option 3, I'd have to either:
1. re-insert the none lookup table snapshot records (like the work order, item and payment records) back into the live tables. This could pose a problem if live lookup table records, like the customer record, has changed since the work order was moved into the snapshot tables.

2. re-insert the payment record back into the live payment table as a orphan just to get a proper ID then move it back into the payment snapshot table.

For now, i have implemented my option 2 concluding that (while it would be nice to keep the original ID's) the original item and payment record ID's are not that important in the snapshot tables. The truly important ID is the work order ID that is attached to every ancillary snapshot table record associated with the corresponding work order record in the work order snapshot table.

Unless you have a better solution, I'm sticking to my option 2.