I have a application that can work offline as well as the same application via web browser.
The web browser is connected to the main DB so always live inserts updates and delete.
The application when offline you can add insert delete into SQL like. A unique id is created for any new item added on the application and can be identified as being from the app from the uuid and the time it was created.
I have a scenario that is confusing me.
Lets say I am offline on the application: Item 1 was created whilst online in the application and exists in main Item 2 was created offline on the application and does not exist in main
Then item 1 is deleted from the web browser so deleted from the main db but still exists locally on the current offline device.
When we go back online we need to sync with the information we have (PHP backend): Item 1 should be deleted from the local app as it was deleted in the main whilst of line Item 2 should be added to main
Additionally when sync is completed a lastsync time stamp is stored locally and sent whenever we sync to main
Now the problem I have with this senario is there is no way to properly differentiate between if we should insert to master or delete from local. Wondering how I could maybe overcome this?
One idea I have is to create a table of deleted uuid's when something is deleted directly on the main db therefore we can tell if we should insert to the main db or delete from local. Is this a good idea and I am wondering on the disadvanges of have a table of deleted id's?
Thanks for your time and help