I have a MySQL database based web application for which I am going to create an iPhone application. This application will allow users to view / insert / update / delete data in their account in the web application.
The easiest way is to create an application for the iPhone simply as an interface for a web application, i.e. Each operation requires a network connection. However, I would like the iPhone application to have its own “stand-alone” version of the database. This will not only allow the user to work offline, but also give a better experience, since having everything local means a faster and more responsive application.
I would handle conflicts using a timestamp and keep the latest version (I already use soft-delete, i.e. when the user deletes the record, I just set the flag), but I did not quite understand how to handle the inserts. Indeed, if the user inserts new elements into stand-alone (iPhone applications) and online (web applications) databases, there will be a primary key conflict (all my tables have an INTEGER field with automatic increment as the primary key). How can I deal with this problem?
I was thinking about having a two-column primary key, one of which is an automatically increasing integer, and the second refers to the “device”. Thus, new entries added through the web application will have the ID 1-web, 2-web, etc. And those created through the iPhone application 1-iphone, 2-iphone, etc. This will combine the two databases without conflict. What do you think of this idea?
source share