I have an application that uses the local SQLite database. I would like to synchronize the data with MySQL database server, because the app is present on other desktops and other mobile devices.
I could have chosen to read the data directly from the remote database but in this way the app not work without an internet connection.
I was wondering how to do it. I thought of this:
- On the server I create a replica of the local database.
- When I add, modify, or delete the data locally I update the remote server.
- When I start the app locally I check on the database server if there are changes or new records.
- In the local database and on the server database I add a field that shows the modification date for each record. This will help me identify the changed records.
- In the server database I add a field that identifies if the record is to be deleted by local app. This will help me to delete any records deleted from other devices.
- If there are records on the server database and not on the local database, I add them to the local database.
More or less is the right way?
What are the best methods to have a correct synchronization?