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

When you need to synchronize data from devices that can work online, you need to be able to handle a temporary ID that will be unique for each device.  When the server updates with an ID, the temporary one can be stripped away, but only by the device that issued it.

Most programming languages have a UUID function that returns a random number that should be unique across all your devices.  Combine this with a local client ID and now both mobile devices can work offline adding widgets without worrying about overwriting someone else data.  Each device creates a random UUID and maintains a copy of it throughout the life of the application.  They may look different by different programming languages and operating systems.  See sample UUIDs below:

PHP uniqid = 4ff2e7f5211cd
Client 1 - Android UUID = 997fbc4e-e7c5-429e-a474-23026e277a92

Client 2 - Android UUID = eb7099ff-c4a2-41a2-8376-7a642c5db3f4

Now two devices can add items offline at the same time

Client 1 Device Data - ClientID is AUTO_INCREMENT, ServerID + ClientID + ClientUUID are the primary key
ServerID#, Item Name, Project Number, ClientID, ClientUUID
=======, =======, =========== , ======, ===============
0              , WidgetA  ,  1001                , 1            , 997fbc4e-e7c5-429e-a474-23026e277a92
0              , WidgetB  ,  1001                , 2            , 997fbc4e-e7c5-429e-a474-23026e277a92

Client 2 Device Data - 

ServerID#, Item Name, Project Number, ClientID, ClientUUID
=======, =======, =========== , ======, ===============
0              , WidgetX  ,  1001                , 1            , eb7099ff-c4a2-41a2-8376-7a642c5db3f4
0              , WidgetY  ,  1001                , 2            , eb7099ff-c4a2-41a2-8376-7a642c5db3f4



Now when Client 1 & 2 publish their data, the server assigns the ServerID

Webserver Data
ServerID#, Item Name, Project Number, ClientID, ClientUUID
=======, =======, =========== , ======, ===============
1              , WidgetA  ,  1001                , 1            , 997fbc4e-e7c5-429e-a474-23026e277a92
2              , WidgetB  ,  1001                , 2            , 997fbc4e-e7c5-429e-a474-23026e277a92
3              , WidgetX ,  1001                 , 1            , eb7099ff-c4a2-41a2-8376-7a642c5db3f4
4              , WidgetY ,  1001                 , 2            , eb7099ff-c4a2-41a2-8376-7a642c5db3f4

Now when the clients download the data from the server to refresh their data, they get as a result

Client Device Data
ServerID#, Item Name, Project Number, ClientID, ClientUUID
=======, =======, =========== , ======, ===============
1              , WidgetA  ,  1001                , 1            , 997fbc4e-e7c5-429e-a474-23026e277a92
2              , WidgetB  ,  1001                , 2            , 997fbc4e-e7c5-429e-a474-23026e277a92
3              , WidgetO1,  1001                , 1            , eb7099ff-c4a2-41a2-8376-7a642c5db3f4
4              , WidgetO2,  1001                , 2            , eb7099ff-c4a2-41a2-8376-7a642c5db3f4

The changes from both devices are kept and there is no overlap.  The flaw with this method is that the UUID needs to be maintained and kept on the device once created.  It needs to be stored in the device app where it can be used by multiple database tables for synchronizing child data which is described below.  Since a UUID cannot be recreated by definition (or is not intended to be) once lost, the UUID is gone.  Some device offer a device serial number, but these methods seem to be inconsistent from manufacturer to manufacturer so a more general method like the UUID is preferred.

There are issues with this system that need to be looked out for on the server.  If a device sends a ClientID + UUID pair without a server ID because it couldn't get a response from the server for whatever reason, we don't want that data duplicated.  So anytime a ClientID + UUID is sent with a ServerID of 0, the server needs to check first and verify that ClientID + UUID are not already used, otherwise it needs to reassign the same ServerID it used previously.  If this is not done, duplicate items will begin to appear on the server anytime a client resends data (because of bad connection, app failure, or any other reason).

Handling Creating and Synchronizing Offline Child Data

The previous synchronization system handles items being created that are only one level deep.  Each item created by a mobile device has only one entry row in a database table.  What if we wanted to store additional information for each item created and link them to the object being created?  This can be done with a fixed amount of levels deep without too much effort.  Having an arbitrary depth is going to require the programmer to be creative to expand on what is shown in this example.

Let's say we want to handle Items in the tables as shown above, but link individual Components to each item.  This is difficult since we don't know what the ServerID of the Item we are creating is, so we need to link to the temporary ID (ClientID + ClientUUID).  I also include the table name as a field so that child data can be used against any type of object.

The ComponentTable will have fields to link it to the object in question.  

Component.RefTable - Text link to identify which Table to link the Component to.

Component.RefServerID - Contains the ServerID from the referenced table of the Item being linked to (0 if unknown)

Component.RefClientID - Contains the ClientID from the referenced table of the Item being linked to.

Component.RefClientUUID - Contains the ClientUUID from the referenced table of the Item being linked to.

NOTE:  It may seem that 

Client Device Data:

ItemTable

ServerID#, Item Name, Project Number, ClientID, ClientUUID
=======, =======, =========== , ======, ========
0              , WidgetA  ,  1001                , 1            , 997fbc4e
0              , WidgetB  ,  1001                , 2            , 997fbc4e

ComponentTable - No primary keys because we can have multiple Components per Item

ServerID#, Comp Name, Project Number , RefTable, RefServerID, RefClientID, RefClientUUID
=======, =========, =========== ,  ===== ,  ======= , =========, ==========
0              , Part1            ,  1001                 , Item      ,  0               , 1                  , 997fbc4e

Client Maintained Child IDs

If the client manages the IDs, it will need to be aware when the ServerID for the Item is received, and update the RefServerID for the Component associated with that CID + CUUID.  This Component can not be sent to the server without that RefServerID in place because the server won't know if duplicates are being created.  So every time a parent object is updated and receives a ServerID, the application needs to search for possible Child objects and update their Referenced IDs appropriately.

ItemTable retrieved from Server

ServerID#, Item Name, Project Number, ClientID, ClientUUID
=======, =======, =========== , ======, ========
5              , WidgetA  ,  1001                , 1            , 997fbc4e
6              , WidgetB  ,  1001                , 2            , 997fbc4e

The app updates the Component data with the given ServerID.

ComponentTable

ServerID#, Comp Name, Project Number , RefTable, RefServerID, RefClientID, RefClientUUID
=======, =========, =========== ,  ===== ,  ======= , =========, ==========
0              , Part1            ,  1001                 , Item      ,  5               , 1                  , 997fbc4e

Now the Component can be sent to the server and given its own ServerID.

ComponentTable - Retrieved after publishing to server 

ServerID#, Comp Name, Project Number , RefTable, RefServerID, RefClientID, RefClientUUID
=======, =========, =========== ,  ===== ,  ======= , =========, ==========
1              , Part1            ,  1001                 , Item      ,  5               , 1                  , 997fbc4e

Server Maintained Child IDs

If the server manages the IDs and additional data column will be required to prevent duplication of child objects.  The objects in the client managed RefClientIDs above do not have a unique index.  This creates a problem in that created Component items can not be uniquely selected.  We'll add its own Client ID to allow this and reuse the existing RefClientUUID since that spans the usage of the entire device application.

ComponentTable

ServerID#, Name, Project Number , ClientID, RefTable, RefServerID, RefClientID, RefClientUUID
=======, ====, =========== ,   ====  ,  ===== ,  ======= , =========, ==========
0              , Part1 ,  1001                 , 1          ,  Item      ,  0               , 1                  , 997fbc4e

0              , Part2 ,  1001                 , 2          ,  Item      ,  0               , 2                  , 997fbc4e

Programming for the client is now a little less complex because we don't need to delay posting of child objects when we haven't determined their RefClientIDs.  All objects without ServerIDs are simply published to the server.  The server figures it out by matching the RefTable, RefClientID, RefClientUUID to the parent object, then filling in the matching RefServerID information.  If the matching object is not available, the child object information can't be used and is simply dropped.  In that case, the client does not receive a ServerID for the child object and simply tries to post it again on the next synchronization attempt.

The server maintained Child IDs have the advantage that all IDs are created and maintained in one place by the server and does not rely on IDs being copied internally by parent objects to child objects on each client device.  This makes upgrading the procedure used easier since it is done in one place.

It could be a disadvantage when identifying and linking child objects is time consuming and there may be a case for doing this on the client device to spread the computation across those devices instead of the server. 

In most cases it is probably better to use the Server Maintained Child ID method where possible.