Maintaining referential integrity between a mobile client and a server

Question

So I have a relatively simple system. A mobile client creates records in a sqlite database that I would like to have synced to a remote SQL server (that is shared with other mobile clients). So when I create a new record in the phone's sqlite table, I then push that change to my remote service through a RESTful API. The problem I'm having, is how do I order the primary keys so that there isn't collisions in the data (i.e. a record in the phone has the same primary key as a completely different record on the server). What is the usual "best practice for referencing the record on the client, and for referencing the same record on the server?

Answer 1

The normal practice is to structure your database with uniqueidentifier keys (sometimes called UUIDs or GUIDs). You can create them in two places without realistic fear of collision.

Next, your Mobile app needs to sync "fact" tables from the server before you can create new rows. When you create new rows, you do it locally, and when you sync again, new rows are added to the server. You can do the same thing with updates and deletes too.

To track inserts you need a Created timestamp on your rows. To track updates you need to track a LastUpdate timestamp on your rows. To track deletes you need a tombstone table.

Note that when you do a sync, you need to check the time offset between the server and the mobile device, and you need to have a method for resolving conflicts. Inserts are no big deal (they shouldn't conflict), but updates could conflict, and a delete could conflict with an update.

There are frameworks to handle this kind of thing, such as Microsoft Sync Framework.

Answer 2

I bet that you absolutely, without question cannot have referential integrity between the two. Specifically, do your users expect the mobile application to work when it is disconnected?

There are two practices for this:

One is to create "provisional" records on the client, and then when you sync them to the server have the central system assign the ID. The client can update the local record to reflect that.

The other is that you distribute ID creation in a way that (normally probabilistically) allows clients to create the ID without collisions.

For that, go to UUIDs - v4 are fairly unlikely to collide.

Otherwise, consider something that puts the unique mobile device ID in the record ID. So, your record ID might be ${imei}-${local sequence number} or something, where the IMEI ensures uniqueness, and the local sequence number is just a normal sequential database ID.

Answer 3

In addition to Daniel Pittman's answer, one possibility would be for the server to assign each client a unique client ID, and make the Client ID a part of the primary key.

Answer 4

I have the same issue with a project i am working on, the solution in my case was to create an extra nullable field in the local tables named remote_id. When synchronizing records from local to remote database if remote_id is null, it means that this row has never been synchronized and needs to return a unique id matching the remote row id.

Local Table            Remote Table

_id (used locally)
remote_id ------------- id
name      ------------- name

In the client application i link tables by the _id field, remotely i use the remote id field to fetch data, do joins, etc..

example locally:

Local Client Table       Local ClientType Table      Local ClientType
                         _id
                         remote_id  
_id -------------------- client_id
remote_id                client_type_id -------------- _id
                                                      remote_id
name                    name                          name

example remotely:

Remote Client Table      Remote ClientType Table      Remote ClientType
id -------------------- client_id
                        client_type_id -------------- id
name                    name                          name

This scenario, and without any logical in the code, would cause data integrity failures, as the client_type table may not match the real id either in the local or remote tables, therefor whenever a remote_id is generated, it returns a signal to the client application asking to update the local _id field, this fires a previously created trigger in sqlite updating the affected tables. http://www.sqlite.org/lang_createtrigger.html

1- remote_id is generated in the server

2- returns a signal to client

3- client updates its _id field and fires a trigger that updates local tables that join local _id

Of course i use also a last_updated field to help synchronizations and to avoid duplicated syncs.