1. Home
  2. Forums Index
  3. Server Side / Databases 1:23 am May 21, 2026

Forum Moderators: open

Message Too Old, No Replies

Question about record ID's when inserting from two different sources.

         

nelsonm

7:42 pm on Oct 24, 2012 (gmt 0)

10+ Year Member



Hi all,

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.

thanks.

LifeinAsia

9:00 pm on Oct 24, 2012 (gmt 0)

WebmasterWorld Administrator 10+ Year Member Top Contributors Of The Month



I don't understand why the client "must" add records to the snapshot table instead of the live table. What's the reasoning behind it?

nelsonm

9:53 pm on Oct 24, 2012 (gmt 0)

10+ Year Member



The client wants to be able to make corrections to the snapshot/closed work order payment records in cases where...

1. (Edit) they make a wrong payment entry and it's not discovered until the work order is closed and moved to the snapshot tables.

2. (Edit or Delete) a payment check bounces after the work order is closed and moved to the snapshot tables. They don't use a check funds checking service.

3. (Add) they close and moved to the snapshot table a work order with a partial or no payment, then later the customer makes a payment or pays the balance.

This is the way the client wants their system to work.

The current system does not allow records associated with a closed work order to be moved from the snapshot tables back to the live tables to be reopened.

LifeinAsia

10:39 pm on Oct 24, 2012 (gmt 0)

WebmasterWorld Administrator 10+ Year Member Top Contributors Of The Month



So even if they make their "correction" to the snapshot table, that correction has to go back to the live table as either A) an updated entry to the original transaction (with the same record ID) or B) a "correction" entry (with a different record ID). Either way, it makes more sense to make the update/addition to the live data instead of the snapshot data. Otherwise, you (and they) are just asking for trouble.

LifeinAsia

10:44 pm on Oct 24, 2012 (gmt 0)

WebmasterWorld Administrator 10+ Year Member Top Contributors Of The Month



Ah, wait- are you actually moving the data from the live table to the snapshot table or copying the data?

If moving, then I guess it's a bit easier.
1) Edit: non-issue, since the record ID should stay the same.
2) Edit/Delete: non-issue, since the record ID should stay the same or get deleted.
3) Add: just add the entry to the live table (to get the incremental record ID) then immediately move it to the staging table.

nelsonm

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

10+ Year Member



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.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

  1. Home
  2. Forums Index
  3. Server Side / Databases 1:23 am May 21, 2026