Q:

Handle synchronisation of data between Android Service and Server via REST API

I am trying to design how to best handle synchronisation of data between an Android application and a remote server through a REST API.
Now it is still early stage but I have my rest server basically running (enough for testing purpose anyway).
I have 3 tables to synchronise (linked as below). 
SHOW >----- SEASON >----- EPISODE
Both server and device have a similar version of these tables and I need to synchronise them FROM device to SERVER (for now and eventually 2 ways). The synchronisation will happen through an Android service making asynchronous calls to the REST API (insert/update and delete) in the background.
Now my main issue is coming up with a logic that will ensure both sides will get updated accordingly. 
insert/update from device to server will be done through a POST request so I thought of having a state flag on all 3 tables that gets populated via ON INSERT/ON UPDATE TRIGGERS (SQlite) allowing the Synchronisation service to work out rows that need to go on the server. Is that the correct approach ? 
It would look something like
CREATE TRIGGER sync_update_show AFTER UPDATE ON show
BEGIN
    UPDATE show SET sync_flag = 'TO_SYNC' WHERE _id = new._id
END

Now for deletion, the service being independent from the main app I though of using BEFORE DELETE SQlite TRIGGERS to populate a "TODELETE" table that the service can browse and trigger delete api calls to the server. Is that the correct approach ?
It would look something like
CREATE TRIGGER sync_delete_show BEFORE DELETE ON show
BEGIN
    INSERT INTO todelete_show
    SELECT * FROM show WHERE _id = old._id
END

Now when it comes to the server to app sync I would just get all records (APi call) and handle the insert/update/delete on the device (browsing through the returned records). But there may be a more efficient approach optimising the bandwidth (the returned JSON might be big).
I welcome any input on that as I do not want to start working on the Service implementation and realise I got it all wrong !

A:

I think your approach using triggers could definitively work, I suggest you use a single "SYNC" table to record all the network operations that you need to make, so that even if you're offline all network operations are scheduled to be executed when possible, and after you have executed a task delete it from the "SYNC" table.
I strongly suggest you implement a ContentProvider to store your tables since you can more easily leverage a lot of Android APIs in your activities/fragments, especially the CursorLoaders and CursorAdapters.
Take a look at contentProviders : http://developer.android.com/guide/topics/providers/content-provider-creating.html
And also take a look at SyncAdapter:
http://developer.android.com/reference/android/content/AbstractThreadedSyncAdapter.html
Also check out this talk about bulding restful apps on android, is a bit old but I think it is still very valid
http://www.google.com/events/io/2010/sessions/developing-RESTful-android-apps.html

