Android (distributed application) primary key strategy
android, database-design, sqlite
Solution
I offered two bounties on this question and didn't find the answer I am looking for. But I spent some time on thinking about the best solution and maybe the question was not open enough and focused to much on the solution I had in mind.
However there are a lot of different strategies available, now (after the second bounty) I think the first question to answer is which data model(s) do you have in your distributed environment? You might have
- the same (or a subset) data model on client and server
- differnet client data model and server data model
If you answer with 1) then you can choose for your key strategy from
- using GUID
- using my approach High/Low
- mapping keys as @user3603546 suggested
If you answer with 2) then only the following comes in my mind
- composite id
I never liked composite id's, but when I think about it (and don't call it composite id's anyway) then it could be a possible solution. Following I want to sketch this solution:
Glossary:
- <client key> ... primary key generated at the client side, so the client chooses the implementation (long _id for Android)
- <server key> ... primary key generated at the server side, so the server chooses the implementation
- <client id> ... ID for identifying the client
- <device id> ... ID for identifying the device, there is a 1-n relation between client and device
Solution:
- Use it only if you have a client data model and a server data model
- The client data model has the fields
- <client key> primary key
- <server key> nullable data field
- The server data model has the fields
- <server key> as primary key
- <client key> nullable data field
- <client id> as mandatory data field to distinguish the client
- When synchronizing from server to client, generate missing <client key> on the client and mark entry as dirty (so that the client id comes to the server at the end of the day)
- When synchronizing from client to server, generate missing <server key> on the server before saving it
- The mapping between client and server data model can be handled by specialised frameworks like dozer or Orika, however the key generation must be integrated when performing the mapping.
I never liked this solution because I always thought in server data model terms. I have entities which live only on the server and I always wanted to create these entities on the client which would not be possible. But when I think in client data model I might have one entity eg. Product which results in two entities (Product and a ClientProduct) on the server.
Problem
I am going to implement a distributed application with multiple mobile clients and a web based server application. So each client and also the server are allowed to generate table entries. Therefore I need unique primary keys over all participants AND I want to be able to generate keys offline. What is the best approach for generating primary keys you are using on distributed environments? For a similar question see 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 am aware that UUID key generation is a good approach for that scenario, but I want to stick to a key with name _id and type long as suggested by the Android platform. I don't want to have a composite id with device (also server is a device) id and local id. This approach wouldn't work well anyway since the server should be able to generate entries for a certain client. In that case I would have to use the device id also on the server. Therefore my current favorite is to build my key with datatype long (I did this before in another project). I think I will use the high/low approach (see for example here What's the Hi/Lo algorithm?) and have a key which consists of: - client id (e.g. ~28 bits) generated from the server - low value (e.g. ~ 4 bits) incremented on client, never persisted - high value (e.g. ~ 32 bits) incremented on client, persisted on client only The client id must be fetched from the server at first start of the mobile application. So the first start needs a network connection. This might be a downside of this approach. When having the client id on the device I can generate keys without a network connection. Normally the high id is a unique value over the database. When a user deinstalls the application and installs it again I have to treat him as a new client and have to give him a new client id. Otherwise I would have to save the current high id on the server to be able to restore it on loss or on reinstallation - not worth the effort. What is the best approach for getting the high id on Android? An autoincrement key is not a solution. I need something like a generator function. And it has to be executed inside its own transaction (not the "user" transaction). Has anyone experiences with that approach on Android and can anyone point me in the right direction? (I only found this answer). What key strategy are you using for your multi client application (online and offline)?
Related problems
- Android: Use UUID as primary key in SQLite
- What's the Hi/Lo algorithm?
- Do i have to use _ID as a SQlite primary key? and does it have to be an INT? (Android Dev)
- How can I use sequences in SQLite?
- 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?