I've got a web application that uses MySQL database, that I'm considering building an apple iphone application. This application allows customers to browse/place/update/remove data on their own account on the internet application.
The simplest way is always to build the apple iphone application simply being an interface for that web application, ie each operation should be attached to the web. However, I'd like the apple iphone application to possess its very own "offline" version from the database. This could not just permit the user to operate offline, but would offer a much better experience, as getting everything in your area means a quicker and much more responsive application.
I'd handle conflicts using a timestamp and keeping the most recent version (I already use soft-deletion, ie once the user removes an archive I merely set a flag), however i haven't quite determined how to deal with insertions. Indeed, when the user card inserts new products on offline (apple iphone application) an internet-based (web application) databases, you will see a principal key conflict (my tables possess a INTEGER area with auto-increment as primary key). How do i handle this issue ?
I believed of getting a 2-colums primary key, one being the car-incremented integer, and also the second being specific towards the "device". By doing this, new records added through the web application might have ID 1-web, 2-web, etc. and those produced through the apple iphone application 1-apple iphone, 2-apple iphone, etc... This could let it merge the 2 databases without getting conflicts. Exactly what do you think about this concept ?
The easiest solution undoubtedly is by using a UUID.
A less elegant option would be to possess apple iphone ids occupy a precise subset from the key space. For example, having a 64-bit key, each apple iphone might be designated a particular pattern from the greatest 20 bits.