Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
1  
The over-arching idea, would be that the client acts as a cache for the web server, with changes being created in the client, then pushed to the web server –  JoeCortopassi Jan 31 '12 at 19:50
add comment

3 Answers

up vote 9 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.