Best way to synchronize offline data to master when deleting items

Question

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

Answer 1

Not sure if and how you solved your problem already, but as I am also working on a similar scenario at the moment, let me share an article/book I stumbled across that isn't too technical and might help you. Below is an excerpt of the chapter "Handling Deletions" in regard to server/client synchronization.

Solutions

Now that you have a better understanding of why deletions are troublesome for the algorithm, let’s discuss what solutions exist, and the various pros and cons that each provides.

Avoid Deletions

The easiest problem to fix is the problem that was avoided. The same axiom holds here – if we can negate the need to perform deletions entirely, then we have avoided a huge potential pitfall. The options for avoiding deletes are very use-case specific, and may not be valid for all use-cases.

Track Deletions

If deletions cannot be avoided, one solution is to allow the records to be hard deleted but to implement some form of tracking mechanism. By this mechanism, the application can keep a list of all the record identifiers that have been deleted, and supply that to the client device in order for them to do likewise.

Logical Deletions

An alternative to hard deletes is logical deletes. For those unaccustomed to this concept, it is simply the idea of adding a deleted flag to each record. Records with the flag set to true should be ignored and treated as if they were not in the dataset.

Each of the above options carries its own unique set of benefits and risks, and no one solution is universally best. For this reason, it is not uncommon for an application of sufficient size to implement a mixture of all three. Let’s discuss each of them in more detail and see how they might be implemented.

For more detailed explanations, please refer to the original source Data Synchronization: Patterns, Tools, & Techniques by Jason Whitehorn.

(Unfortunately the book is not 100% accessible for free and the registration functionality is also deactivated, but there are quite a few free chapters.)