Example tables layout:
Items table has auto-increment primary key 'id'
TransactionEntry table has its own auto-increment primary key 'id' and a foreign key 'item_id'
Example of how remote and local database foreign key relations are incorrect after sync using CDB synchronizer:
8:00am -first installation of database-'item' tables auto-increment 'id' columns match with id last record value of '6'
locally the following products are added:
11001 short sleeve t--gets added with primary key in 'item' table 'id' of '7'
11002 long sleeve t--gets added with primary key in 'item' table 'id' '8'
remotely the following products are added:
21001 hipster jeans- --gets added with primary key in 'item' table 'id' of '7'
31001 overalls--gets added with primary key in 'item' table 'id' '8'
remotely someone orders 21001..so TransactionEntry table records sale of "item_id" of '7', but after synch with our local server,
product with "item_id" of '7' is "short sleeve t".
9:00 -synch takes place...item_id foreign key isn't accurate because of independent auto-increment values..
whenever a product is ordered, the TransactionEntry table will record the product's ID column thats available in it's own local copy... after synch, the 'item_id' field will not match the 'Item' table id field and the data about the transaction's product is lost.
I have read of solutions involving staging/temporary tables to cascade update foreign keys before synching into main database, but hopefully there is a more elegant solution for this. If this is only way, will it be reliable? foreign key mix-match seems like could cause havoc.Hi,
The way i'm using is to create my own table with two columns...
The columns are Old_ID and New_ID
Before syncronization u inserts all elements from the synchronized table there!
After appending ur data into the main table u need to identify using other field /s/ which id corresponds to ur ancient id /with Update action query
The last step is appending the info from the table with foreign keys linked with ur temporary table. Instaed using the old foreign key u need to put the new entry and the information will be at its place!
Hope this helps as conception!
:)
No comments:
Post a Comment