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?
The normal practice is to structure your database with
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.
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
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.