C# database syncing application identification issue

Question

I'm trying to sync data between SQL Server and SQLite (where the structure of the databases match) within a C# application.

The app has the ability to update, add, remove information, and so does the handheld (motorola rfidreader), so either-or could've have possibly updated the information, and the application won't know until the syncing begins.

My issues is with knowing what row within the database has been added/removed/ or updated. I have a history table that keeps track of when CRUD operations occur, but the only unique identifiers within each database is an auto-incremented ID column.

The problem is, if I've added a row on the handheld, and then try to sync the two databases, and add a row on the server as well, the ID values will (probably) be off. That means I can't search for a ID and expect 100% of the time that the ID correctly matches both databases. mismatched ID's between the databases will throw off the Remove and Update functions as well, since I don't know if I'm working on the right row item.

How can I sync these two databases and keep the IDs intact?

The only solution I've come up with so far (and I'm not convinced its the best option) is to have a table in the server database that keeps track of any ADDS when syncing. So essential there is a column for which table is effected, and the HandheldRowID and the corresponding ServerRowID - so I'd have a connection to each, just in case they are not matched correctly

Answer 1

IF you cannot change the PK field types (as indicated in my comment, I recommend you use a uniqueidentifier column) then you (as always) have a couple of options. I am going to highlight my favourite first.

In order to preserve referential integrity, and the auto-incremented Id field, you should save a state with your history table. Then commence a merge between the two databases when the work is complete. The handheld should send it's history table to the server, which should make the appropriate changes and inform the handheld of any specific modifications it would need.

Example of your history table on the handheld:

Table    Action    Id
-----------------------
A        Add       1502
A        Edit      1502
A        Delete    134

Same table on your server:

Table    Action    Id
-----------------------
A        Add       1502
A        Delete    138

Now, you and I both know that the record 1502 in table A on both devices is not the same record. So what do we tell the server?

Well, the server should simply receive the devices history table, and then enumerate each action in it. On an Add, it should create it's own auto-incremented Id value (1503 in this case). It would then add that record to it's history table, with the new Id. Once done so it would request the actual data for that record from the client device, and save it to the table.

Then, the server would see the Edit record for the same Id, and dispose of it. (No sense in recording an edit we already have the latest for.)

Then the server would see the Remove record, and remove that Id from it's own database. It would then add a record of it having done so to the history table.

Once all this is done (and the records added to the history table on the server), it would send a copy of it's history table to the handheld, which would then apply the same changes. Note: the handheld should rollback the changes it made to the extent it can. (If Add 1502 is present, then Remove 1502.) It would then receive the server's copy of the changes. Once done so it would do the same thing the server did, except that it need not worry of the auto-incrementation on the Id columns, as the server handled that.

This process of course can be extended (albeit with more complexity) to having more than one handheld.

Using GUIDs/UniqueIdentifiers

If you can use a GUID or uniqueidentifier for the PK, then this process is incredibly simple. Just merge the changes. The chances of any GUID/uniqueidentifier values overlapping is minimal to none.

My favourite quote (from the Wikipedia article):

They may or may not be generated from random (or pseudo-random) numbers. GUIDs generated from random numbers normally contain 6 fixed bits (these indicate that the GUID is random) and 122 random bits; the total number of unique such GUIDs is 2¹²² (approximately 5.3×10³⁶). This number is so large that the probability of the same number being generated randomly twice is negligible; however other GUID versions have different uniqueness properties and probabilities, ranging from guaranteed uniqueness to likely duplicates. Assuming uniform probability for simplicity, the probability of one duplicate would be about 50% if every person on earth as of 2014 owned 600 million GUIDs.

GUID References:

StackOverflow Question on Generation Algorithm

Wikipedia Article with some Good Stats