Online storage / sync of large iPhone user databases: perform on server or on device?
I'm working on an online user database backup / sync system for a popular SQLite-based iPhone flashcard app; this is fairly large-scale - we could pretty quickly get to 100 million individual flashcard records or more (10,000 users, 10,000 cards each) - and we need to design the system with that sort of scalability in mind. But we're not really in the web-based software business, and at least initially we're not planning to actually give this much of a web interface except for account administration - all of the actual manipulation of user data would occur offline.
So it seems like we have two basic options for how to implement this:
Perform the sync on the server; store all the users' data in a MySQL database (or a bunch of them as we scale up) and download / upload lists of changes only.
Perform the sync locally; download the latest version of the database from the server, check for / apply changes on-device, then upload the merged database.
It seems like the local sync option would mean a lot less programming work and much lower hosting costs on our end, but would also mean significantly more bandwidth usage for our users - probably something on the order of 2-4 MB for each sync operation versus a few dozen K for a web-based sync.
Does anybody feel like one of these options is clearly better than the other? Is there some other way of doing this we haven't considered?
I could be way off with the way this works but, as this is synchronisation, why would the local copy of the database not be able to record a list of changes that can then be replicated on the server side? THis only works for a single device of course not syncing multiple devices to the same data set.
The changes could be noted in various ways: eg you could checksum the records on the iPhone and checksum them again when synchronising, then upload the ones that have changed, deletions would need to be recorded seperately.