I’m looking to create a database application that has a local database (so it can be accessed offline) as well as a “cloud” database for both backup and as a central controller to synch the app between the desktop, iPad, and potentially other users. I’ll be using the great Bkeeney ActiveRecord with a local SQLite and ideally CubeSql for the cloud.
If anyone has any particular suggestions about how to implement this, or things I should avoid/consider/never ever do/etc, I’d appreciate the advice. It’s mainly how to synch it that I’m most concerned about. What to do if part of a file gets modified in one location and in another location before either one synchs?
Sync is never easy. It’s made considerably easier if each user has their own database in the cloud they sync with. If you have to sync multiple users to one database then you have a lot of work ahead of you. It would be helpful to know more about the specifics.
I’ve been thinking about this a bit more. If you have an option I would make the client work online first and then add an offline mode. Then you can be more selective about what data actually needs to be stored offline vs online. if you start with the entire application offline then you undoubtedly will want to sync everything otherwise the sync is not really consistent with other clients on other machines.
Collisions are the most challenging aspect. Your question to consider modification dates is logical but ultimately won’t work. Most internet devices are not time synced so you cannot trust the client. If the client is the master database and you are syncing some elements to the cloud then you have to make the cloud the ultimate authority for that subset of data. The problem is related records. Say a user offline edits a customer and adds a couple orders. However before that happened back in the office our friendly terribly underpaid overworked IT guy accidentally deleted that customer on a trouble ticket. He didn’t realize he did it and now the records are marked as deleted or actually deleted. You go to sync and the cloud rejects the customer and the orders and your client app gets back “error”. You forgot to address every possible collision and instead of working through it the app assumes its corrupted and forcefully downloads the latest from the cloud. Those orders are gone.
So the key comes down to immutability: Track every important change, never delete vital data, use truly device unique level identifiers (GUID/UUID, etc). You have to build the entire thing like every possible error can and will occur and recover from that defensively without destroying the users trust that the data is safe.
Good luck. 
Thanks Phillip. I think you’re right. Maybe I’ll just do an “offline mode” that isn’t editable but allows access to the records.