Two-way sync between iPhone application and web application

Question

I have a web application that relies on a MySQL database, for which I am thinking of building an iPhone app. This app would allow users to browse/insert/update/delete data on their account on the web application.

The easiest way would be to build the iPhone app simply as an interface for the web app, ie each operation requires to be connected to the web. However, I would like the iPhone app to have its own "offline" version of the database. This would not only allow the user to work offline, but would also provide a better experience, as having everything locally means a faster and more responsive app.

I would handle conflicts by using a timestamp and keeping the latest version (I already use soft-deletion, ie when the user deletes a record I simply set a flag), but I have not quite figured out how to handle insertions. Indeed, if the user inserts new items on both offline (iPhone app) and online (web app) databases, there will be a primary key conflict (all my tables have a INTEGER field with auto-increment as primary key). How can I handle this problem ?

I was thinking of having a two-colums primary key, one being the auto-incremented integer, and the second being specific to the "device". This way, new records added via the web app would have ID 1-web, 2-web, etc. and the ones created via the iPhone app 1-iphone, 2-iphone, etc... This would allow to merge the two databases without having conflicts. What do you think of this idea ?

Answer 1

The simplest solution by far is to use a UUID.

A less elegant solution is to have iPhone ids occupy a defined subset of the key space. For instance, with a 64-bit key, each iPhone could be assigned a specific pattern of the highest 20 bits.