Synchronization Strategy from Offline Mobile Devices to a Main Webserver - Part 1

I have a website with some business data on it, let's say a list of Items and it runs on MySQL with the following table:
ID#, Item Name, Project Number
====, =======, ===========
1       , WidgetA  ,  1001

(NOTE: when synchronizing items between a client Android device or iPhone, I use other fields in my table for date received and others.  They have been removed for simplicity.)

If I need to add items to this database from the website, it is quite simple.  Setup the ID column as autonumber.  Now all the added items will get unique IDs and everyone is happy.


ID#, Item Name, Project Number
====, =======, ===========
1       , WidgetA  ,  1001
2       , WidgetB  ,  1001

Now what if I want to use a mobile device to add items from an application that allows me to do it offline.  Since I still need some way to identify the new item, I'll add another column that indicates the ID used on the client device.  Everything I create on the client device while offline will have a Server ID of 0 and Client ID of X.  When the server gets the data and creates the new ID, it sends it back to the client device so that they can keep in sync.

Adding items Offline1 and Offline2 looks like:

Webserver Data - ServerID is primary key and AUTO_INCREMENT
ServerID#, Item Name, Project Number, ClientID
=======, =======, =========== , ======
1              , WidgetA  ,  1001                , 0
2              , WidgetB  ,  1001                , 0


Client Device Data - ClientID is AUTO_INCREMENT, ServerID + ClientID are the primary key
ServerID#, Item Name, Project Number, ClientID
=======, =======, =========== , ======
1              , WidgetA  ,  1001                , 0
2              , WidgetB  ,  1001                , 0

0              , WidgetO1,  1001                , 1
0              , WidgetO2,  1001                , 2


Now when the client publishes the data to the server, the server assigns new IDs



Webserver Data
ServerID#, Item Name, Project Number, ClientID
=======, =======, =========== , ======
1              , WidgetA  ,  1001                , 0
2              , WidgetB  ,  1001                , 0
3              , WidgetO1,  1001                , 1
4              , WidgetO2,  1001                , 2


Next the client queries the webserver for changes and the webserver sends the two new IDs back to the client.  Since the ID is now assigned, the client can clear the ClientID and reset it to zero.  This can be left alone, but if you know you only need to publish items with a ClientID greater than zero then you will save time and bandwidth by resetting it when it is no longer needed.


Client Device Data
ServerID#, Item Name, Project Number, ClientID
=======, =======, =========== , ======
1              , WidgetA  ,  1001                , 0
2              , WidgetB  ,  1001                , 0

3              , WidgetO1,  1001                , 0
4              , WidgetO2,  1001                , 0



Ok, this method works pretty good if you have only one client, but quickly breaks down if two or more try to add items at the same time.


Client 1 Device Data
ServerID#, Item Name, Project Number, ClientID
=======, =======, =========== , ======
0              , WidgetA  ,  1001                , 1
0              , WidgetB  ,  1001                , 2




Client 2 Device Data
ServerID#, Item Name, Project Number, ClientID
=======, =======, =========== , ======
0              , WidgetX  ,  1001                , 1
0              , WidgetY  ,  1001                , 2


Now when Client 1 & 2 publish their data we are going to get strange data on each client depending on how our code is written.  The way around this is to use something called a UUID.  This will be examined in the next post.