2

Client DB - CoreData (iOS)
Server DB - MySQL

I am trying to achieve data synchronisation between client and server but the complicated part is that the schema is highly relational. I was going through couple of synchronisation patterns already in use and looks like most of them are based on a NOSQL or schemaless DB. Wondering if there are any patterns of synch for a highly relational data. I have already gone through couchbase, dropbox sync api, wasabi synch etc. Following are the concerns

1) By highly relational data it means, there are several tables which are related to each other and Create/Update happens on all the tables. Right now I am planning to do seperate CRUD requests for each table. Is that a good approach? But the problem is that there should be a strict ordering of the requests because the changes in table-3 cannot be processed before the table-2 data is received. This relationship is making it hard to synch.

2) Change tracking on the client. what would be the best way to identify the changes in a particular table(CoreData Entity). I am planning for a delta approach where only the changes in similar kind of objects will be uploaded at a time.Any insights /links to it?

3) Data Merging/ConflictResolution - I am stumbled upon this part. 1 way would be to have the modified timestamp in each object, but what if the devices dates are not in sync or manually changed.

I wanted to know the implications/challenges in such a synch pattern with RDBMS backed server or any alternative approaches.

Problem #1 Explained

Assume there are 10 tables and APIs expose CRUD requests for these 10 tables. 1 Request will do only C/R/U/D of any one table. So my question was is this a good approach to design APIs like this when it comes to offline syncing of data. For e.g. Consider a relational data

Organization->Employee->Department->Project

Assuming some objects of these 4 tables got created offline. Now we need to sync data to server when network is back. So it will be like Create/Update Organisations First, Once it is over Create/Update Employee so that it can be linked to Organisation. So basically everytime a C/U/D will be issued from the top->bottom level objects. So my question is whether this is good approach in a Sync Problem. Because if the data was not relational we could have uploaded the changes in all the tables in a single C/U/D API Call.

Sj.
  • 1,674
  • 2
  • 15
  • 23
  • What does your server API look like? Or is that also not yet designed? – Tom Harrington Aug 01 '15 at 23:35
  • @TomHarrington server is running on Heroku and API's are designed in nodejs – Sj. Aug 02 '15 at 01:17
  • 1
    I actually meant what do the API calls look like, not what frameworks they're built with. It sounds like you're concerned with trying to replicate MySQL requirements in your client app, which would be unusual. – Tom Harrington Aug 02 '15 at 15:55
  • possible duplicate of [Client-server synchronization pattern / algorithm?](http://stackoverflow.com/questions/413086/client-server-synchronization-pattern-algorithm) – philipxy Aug 04 '15 at 19:24

1 Answers1

0

It seems that you might not be aware of typical relational DBMS facilities and protocols that

support simultaneous write access by multiple sessions, making them suitable for multi-user, highly concurrent, and OLTP applications.

1) Your API to access MySQL allows you to make your changes atomically (all or nothing) via a transaction. Within that transaction you should update as many tables as possible simultaneously but can sequence such changes as necessary. By locking tables as you use them then unlocking in the reverse order you avoid deadlocks. You can request that only parts of tables that a transaction knows it could possibly change are locked so that non-overlapping clients can proceed concurrently.

2) Your schema can explicitly record redundant delta information that you get the DBMS to calculate on updates, or it can record sufficient past changes to calculate deltas on request. Your client can give the DBMS its transaction data and the DBMS can return relevant info based on it and the past. You probably do not need to and should keep any persistent state on your client. That is what the server database is for. The client database is a buffer for it and user info.

3) You can use an explicit client serial transaction id so that client plus id indicates what order the client thinks its transactions were sent regardless of its clock.

I wonder how much you have googled.

Community
  • 1
  • 1
philipxy
  • 14,867
  • 6
  • 39
  • 83
  • Thanks for your response. I have edited my question with more details. #1 - Please refer the Problem 1 Explanation. My question was whether the approach good or open for any other alternatives. #2 - This is solely related to iOS CoreData as the changes should be tracked on client. – Sj. Aug 04 '15 at 20:44