Angular App/PHP - Best way to synchronize local and remote databases-CodePudding

I have an angular application that is available on IOS and Android and also a website version of the same. The website version updates the master DB in real time. The apps currently call my api's to read from the master DB and so internet is always required.

Now I want to also add offline usability to the apps.

So I need all the users data on there devices locally regardless of how many devices the user has the app on. My question is mainly to do with how I can go about synching the best way. The user will have the ability to add, edit and delete there data whilst offline and at the same time they could also be doing the same on the live website version.

I thought of putting a timestamp, then I can receive the data with the timestamp, I would be able to compare the timestamps with each data entry, then, update or add the information to my local database.

What's the best way to do it? Should I put a timestamp to each entry I put in the remote and local server? Or does anyone have any other solutions for this problem. As sync with timestamps can be good if it happenes periodically but mine essenstially need to be live sync so when the user does have connection then the both local and master are updated immediatly and consider some of my users have more than 8000 rows speed of the sync's is very important

For context, the application/ website is a record keeping system so the user can add many rows that can be edited deleted etc.

The master DB is mysql and the apps I am planning with SQLite

Thanks in advance

CodePudding user response：

Distributed systems that not always have contact to the server should have a guid, that identifies every row in the whole database, distributed and main server., with a unique id.

When the client has connection again, you can use a REST Api to send all new data.

Additional you need timestamp columns and of course the last time you have send the data, so that you can send only the new and changed data to the main server.

If you can use Mysql on the server and local. You could use the mysql replication, to keep all data synchronized, but developing your own system will garantee that only the minimum will be send.

A small test system is quickly established, but you need then also to imolemt security messures, so that nit everyone can change or insert data.