What is the best primary key strategy for an online/offline multi-client mobile application with SQLite and Azure SQL database as the central store? What is the best primary key strategy for an online/offline multi-client mobile application with SQLite and Azure SQL database as the central store? azure azure

What is the best primary key strategy for an online/offline multi-client mobile application with SQLite and Azure SQL database as the central store?


I've considered this question at length came to the decision that a GUID is usually the best solution. Here's a little information on why:

Identity

The Identity option sounds like it removes all the negatives, but having implemented a Single Page Web App that implemented this system, I can tell you it adds a significant amount of complexity to the code. A temporary id can spread through your client side data quite quickly, and it's really hard to create a system that has no holes in it when it comes to finding every single possible usage. It usually leads to application and data specific hard-coded information to track foreign keys on the client (which is tedious and error prone as the database changes and you forget to update this information). It also adds a lot of overhead to every sync, as it might have to run through multiple tables each sync to check for temporary ids. There might be a better way to implement this system, but I haven't seen a good approach that doesn't add a ton of complexity and possible ugly error states in your data.

Composite

The composite approaches also add a lot of complexity to your code in generating session ids and creating ids from them, and they don't really offer any advantages over GUIDs other than you can guarantee that it's unique - but the thing is, a GUID is theoretically unique, and while I was scared of the fact that there is a possibility of repeats, I realized that it was an infinitesimally small chance and there's actually a really easy method to handle the small possibility that it's not unique.

GUIDs

My biggest worries about using a GUID were

  1. they have a large size and aren't traditional ints, which will make transferring large bits of data slower and degrade database performance
  2. if you actually ever do run into a conflict, it can ruin your app, so you have to write complex code to handle a situation you will probably never use.

Then I realized that in an offline style web app, you're not usually transferring large amounts of data at once because it's all stored on the client.

You also don't worry about server database performance much either because that's done behind the scenes in a sync - you just worry about client side data performance.

Last, I realized that handling a conflict is really a trivial thing. Just test for a conflict and if you get one, create a new GUID on the server and continue with the operation. Then send a message back to the client that causes the client to throw up a little error message and then deletes all client side data and re-downloads it fresh from the server. This is really quick and easy to implement, and you probably already want this as a possible operation on an offline web app anyway. While it might sound inconvenient for the user, the likelihood of the user ever seeing this error is almost 0%.

Conclusion

In the end, I think for this type of app, GUID's are the easiest to implement and work the best with the least possibility for error and without creating overly complex code.

If your application doesn't have to run offline, but you have a client-side database for performance or other reasons, you can also consider throwing up a loading gif and pausing client side execution until the id is returned via ajax from the server.


The key (pun intended) thing to remember is to simply have a unique key for each object you are storing on the persistent store. How you handle the storage of that object is completely up to you and up to the methodology of how you access that key. Each of the strategies you listed have their own reasons for why they do what they do but in the end they are storing a key for a certain object in the db so all of its attributes can be changed while retaining the same object reference in the database.