14

I have an SQLite database on Android and a MySQL database on a server. I want to synchronize these databases when a user edits data on their phone or edits data on a website.

I know how to update the MySQL database on the server when a user makes changes on their phone but I don't know how to update the Android database when a user makes changes on the website.

I have read into push notification and believe this to be a good path to follow but I have a few questions about it:

  1. When a user updates data through a website it will send a push notification to that user's phone saying changes have been made. Can this push notification trigger to update the Android's database with the new changes made on the Server database?

  2. What if a user turns off push notifications? Will I still be able to trigger for their Android database to be updated?

I have also read up on SQLite and MySQL database synchronization and found this post SQLite and MySQL sync but did not find the post helpful for my situation.

Are push notifications a good way to go or should I be using a different approach?

In a nutshell - I want a way for the Android device to detect changes on the MySQL database and update its SQLite database without the user initiating the synchronization.

Community
  • 1
  • 1
Gary Holiday
  • 3,297
  • 3
  • 31
  • 72

2 Answers2

1

Token based pagination approach.

Assumptions: or calls you need to take

  1. One of the databases will the source of truth, in case of differences in the two, which data is true, and which will be overwritten? - assuming remote database is source of truth
  2. What's the frequency of data changes? - assuming its not realtime critical
  3. How much stale data are we OK with dealing on the app. - assuming we're OK with a few minutes of difference

How to ensure data is consistent

  • Find a method to associate a token, which can be used to identify till which record is the data in sync. This is important no matter how assured you are of web requests, they will fail. So the best method is to send the last token that have stored, and the web endpoint will return data from that token to the latest value.
  • If the volume of data is too much here, sending chunks > sending all of it. Chunks work the same way, based on tokens.
  • These tokens can be simple PK auto increment column as well.

How to deal with time difference, stale data

If your application demands some data to be near realtime, better to categorize data based on a fiew screens, and whenever the user comes to the said screen, send a request in background to fetch related data columns only. Will ensure the data stays in sync w.r.t to the important columns. This is a classic push/pull approach. Can also be done on the splash screen itself.

  • as a rule of thumb, if you need something urgent, pull.
  • if it can wait, wait for a push.

Push notifications are OK as far as:

  • they should be silent.
  • there'a a limit on the number of push notifications that you can send
  • have costs associated
  • what's the fail - check mechanism? What if the requests fail?
Yash Kumar Verma
  • 9,427
  • 2
  • 17
  • 28
0

I'm afraid I've not used push notifications. But a solution might be: You could create an early method call to an Asynchronous polling event from the launcher onCreate() that looks up the server to see if any changes have been registered (though an API of some sort) in the MySQL, and then update the SQLite that way? Since it's the first thing that happens on launch, technically the user isn't initiating it. Granted this won't update during use of the app, unless you repeat poll at regular intervals?

iaindownie
  • 1,046
  • 12
  • 28