I'm a bit confused about how to identify the elements in my application.
I have a rest backend with an sql database. I'm currently using numeric (autoincrement) ids as primary key and use these for foreign keys. I also defined uniques based on semantics.
My client is an app where I, for the most part, am mirroring the remote database. The goal of this is both to have a cache (performance) and to make possible to use the app offline. The offline usage can be temporary or permanent (user can use the app without ever connecting to the server).
So far I have been implementing the client with online usage in mind. So when the user creates a new item I would send a POST to the server, the server creates the item and gives it an id, and sends me back the item with the id, and I store it in the local database. This way I will use this id as foreign key also in the client database.
But the offline usage doesn't work like this. When I add an item without server I'll have to give it an id in the client. So this is immediately available to be updated, used in a foreign key, etc. Furthermore when offline is just temporary, that is the user connects later to the server, I have to synchronize the items which were added offline. If I'm using client-side ids, I would have to reemplace these in the server with server-generated ids, otherwise the database would look inconsistent and it may (though highly improvable) happen a clash. Or just use always clientside ids?
So my question is, how is this handled normally? I can think of 4 approaches:
Drop the ids entirely and use only uniques. I can get even updates working with this. For example a unique can be a name. But this can lead to issue as follows - if user was online and created an item. Goes offline and updates the name of the item, which is the unique, 2 times. I would have to store in the client the last value of the unique when a synchronisation happened in order to do the update in the server. And cascade update. Besides of this unique also occupies more space than numeric id in the database.
Drop the ids only in the client. So in the client I use only uniques (name, etc) and I send the server old-new value for updates, which is the same as in 1. The server uses ids internally but never sends this to the client.
Use the server ids and do a sync call as soon as the client goes online where the client-generated ids are replaced with server-generated.
Generate the ids always in the client - similar to described here maybe. (to garantee uniqueness among users e.g. device UUID + timestamp, but this leads to relatively long ids, not sure about space/performance implications here). And use them in the server instead of autoincr id. Or maybe use both?