Hello, I'm building an iPhone app / web application, and I'm trying to think through the best way to have two-way sync of the databases. I'll probably go with sqlite on the iPhone and mysql on the web (as that's what I know) but am unsure of how to handle keeping them in sync.
Here's a sample schema:
index_id(autoincrement) | title | amount | user_id | created(datetime)
This is an issue because you have to deal with certain situations like. A = Server, B = iPhone
A - Adds new entry
B - Adss new entry
A - Get B's entry
B - Get A's entry
A - Update entry
B - Delete A's entry
(Which one do you use, do you sync over the change to B that A made or do you delete the item on B that A made?)
The syncing part can be a simple push/pull style http request, it's the logistics of how to properly keep them in sync that raises concerns.
For the How to: Simply have the iPhone check a server page(update.php) for any changes along with it's changes. Update the server with the changes the iPhone sent and update the iPhone with any changes that request sends back(using JSON or XML).
Why don't you have your web application provide a secure/authenticated web service for your iPhone clients?
For example, iPhones would communicate with this service (using JSON,XML,SOAP,whatever) and you only have to maintain ONE database.
Okay, I've had two subsequent ideas for how to approach this, thought it'd be better as an answer than an edit:
1) You have two databases, one for the phone and one for the web app. The schema would look like this:
index_id | title | amount | user_id | created | environment | foreign_id
So let's say I have an entry on my mobile device: 1,'Title',2.00,1,NOW() and an entry on my web app, 1,'Something',5.00,1,NOW() . To avoid these two things conflicting, the web app will add a row saying 2,'Title',2.00,1,NOW(),'mobile',1.
In this way I can maintain all index_id's correctly. This seems like an absolute nightmare to maintain and get right.
2) What if one were to designate a DB (say the web app) as the master and the device as a slave, so that you had one table on the web app and two tables on the device. For the device, you'd have one table 'queues' which, upon network connectivity, would update the live web app database (at the same time clearing itself out), and then the WEB APP database would sync, one way, back to the device's second main table.
Prior to syncronization, the business logic on the device would have to treat the two local tables as one. This seems like an easier beast to tackle than the above.
Just replicate the schema in both places and toss a timestamp in each row on every table. Then have the client send up its newest timestamp and have the server return rows newer than it.
Easiest way...