Implementation details of database synchronisation API

Question

I want to achieve a database synchronisation between my server database and a client application. The server would run MySQL and the applications may run different database technologies, their implementation isn't important.

I have a MySQL database online and web accessible via an API I wrote in PHP (just a detail).

My client application ships with a copy of the online data. As time passes my goal is to check for any changes in the online database and make these updates available to the client app via an API call, by sending a date to an API endpoint corresponding to the last date the app was updated, the response would be a JSON filled with all new objects and updated objects, and delete IDs, this makes possible to update the local store appropriately.

Essentially I want to do this: http://dbconvert.com/synchronization.php

My question is about the implementation details.

1. Would I need to add a column to my database tables with a "last modified" date?

2. Since the client app could be very out of date if it's been offline for a long time, does that also mean I shouldn't delete data from the online database but instead have another column called "delete" set to 1 and a modified date updated appropriately?

3. Would my SQL query simply check for all data with a modified date superior then the date passed into the API request by the client?

I feel like there's a lot more to it then having a ton of dates everywhere. And also, worry that I will need to persist a lot of old data in order to ensure that old versions of the client app always have the opportunity to delete parts of their data when they are able to sync.

Answer 1

There are different ways to do it but in general you have 2 choices:

Rough imports: overwrite what you have with the new data coming in. So a full new import of the data.

or the alternative:

Track changes: Don't just use a modified date field but really track all changes. Then let the changes be downloadable with a from date. So, the client asks for all changes since the last time it asked. Then it will apply all changes and you can handle the arising conflicts.