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 have the same issue with a project i am working on, the solution in my case was to create an extra nullable field in the local tables named remote_id. When synchronizing records from local to remote database if remote_id is null, it means that this row has never been synchronized and needs to return a unique id matching the remote row id.
In the client application i link tables by the _id field, remotely i use the remote id field to fetch data, do joins, etc..
This scenario, and without any logical in the code, would cause data integrity failures, as the client_type table may not match the real id either in the local or remote tables, therefor whenever a remote_id is generated, it returns a signal to the client application asking to update the local _id field, this fires a previously created trigger in sqlite updating the affected tables. http://www.sqlite.org/lang_createtrigger.html
1- remote_id is generated in the server
2- returns a signal to client
3- client updates its _id field and fires a trigger that updates local tables that join local _id
Of course i use also a last_updated field to help synchronizations and to avoid duplicated syncs.
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.
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