0

I’m writing an android app that will sync with a MySQL db on my webserver (there will also be a website reading from/writing to the same dB). The android app will store a copy of the data locally in a sqlite db to provide access while offline. If the user creates a row while offline, that record will be uploaded to the server the next time a data connection is available. I’m designing the app and website myself so I have the ability to set it up as I see fit (meaning it doesn’t have to conform to someone else’s server).

The sqlite db will have a column for id (which will represent the id as stored on the server) and a localID column. When the server receives the data, it will acknowledge the new data by returning an array (in json format) of the id numbers as stored on the server.

What would be better for this type of scenario: a transaction-safe engine or non-transaction-safe (such as isam)? It’s my understanding that isam would be faster and take less space to store but I can’t deal with losing data. I’m thinking that if the android app doesn’t receive the confirmation, it would resubmit the data. It seems like that would prevent data loss but I need a second (more-experienced) opinion. If you would go with a transaction-safe db, which would you recommend as I’ve never worked with one?

TIA!

doni49
  • 45
  • 7
  • I voted to close your question because here it is either primarily opinion-based or too broad. You have a complicated problem to solve. Your first thought on it is good an localid. But you lack the exceptions case. Like: What if someone else added the same registry from another device? What if someone delete a registry that you edited on your device? What if you delete a registry that was updated from your site? There are many scenarios that you should be aware of and decide what should happen in everyone of then before trying to decide if your system whould be transactional or not – Jorge Campos Dec 15 '15 at 18:43
  • Though transactional is almost always a better choice. – Jorge Campos Dec 15 '15 at 18:44
  • I understood that it's based on opinion -- as someone without much experience using MySQL, I was hoping to get info from people with experience as to why one would make more sense than the other. – doni49 Dec 16 '15 at 01:27
  • I hit enter forgetting that doing so is the same as submitting so here's the rest of the comment: I have already pondered the thought about someone editing data from another device and although the final plans aren't nailed down, I have somewhat of a plan that I need to iron out. I didn't mention that because I thought that would make it "too broad" and not specific enough. – doni49 Dec 16 '15 at 01:35

1 Answers1

1

A real database should be your default choice until you've seen that it's not fast enough.

Consider using UUIDs to generate IDs on the client that are guaranteed to be unique on the server.

Have you thought about how you would handle updates from multiple devices that both had off-line changes? You should consider some known patterns for dealing with this kind of synchronization.

  1. Stack Overflow question
  2. Data Replication book
Community
  • 1
  • 1
Jeremy Stein
  • 19,171
  • 16
  • 68
  • 83