designing a portable app with local lightweight database that sync with a central server

Question

how do you go about designing an application that allows the sales team to have a local copy of the information and then sync with the server whenever they are connected back into the office network?

how do you deal with edit conflicts while syncing ?

Answer 1

Conflict resolution and data merging is a tall order.

I had to do a project recently with this same kind of issue doing VCard merging with a webservice.

We have a good paradigm already in the software development world: source control!

You need a version or timestamp with the data that is always updated when the data is edited. You need to store this version id when you check out the data so you know the version you received. Then in your offline app, you need a method for detecting changes that happened locally and flag them in your app (know what was added, removed, or edited locally).

If you have that information, when you are sending up local changes that happened, you can extrapolate that a change occurred remotely on the central server at the same time a user made a local change while offline to the same data. Using that you can bring up some kind of call to action for the user to merge the data.

Below is the logic that used for the webservice I created if it helps.

Sync operation pseudo logic:
1.  The client calculates all local changes that have occurred since the last sync according to the local sync data file.  
  a.    Updated contacts - contacts that are different since the last sync according to the local repository’s data
  b.    New contacts - contacts that are not in the local repository’s data but have appeared since the last sync locally
  c.    Deleted contacts – contacts in the local repository but are no longer on disk
2.  For any updated or deleted contacts, the client will call and check to see if the file was modified on the server since the last time there was a sync locally to try and find any possible sync conflicts. 
  a.    If a file was modified or deleted locally and at the same time modified or deleted on the server since the last sync, then a call to action dialog should be displayed asking the user to choose between updating everything to use the current web service version or the current local version.
    i.  If user chooses the server change set, then the local version should be overridden with a GET call to the web service
    ii. If the user chooses to take the local change, then an update (PUT) or destroy (DELETE) call should made to the web service
  b.    If the file was not modified remotely then it should automatically be put on the server
3.  For any new contacts created, the client should do a new (POST) call to the server automatically for each one and add a new line to its repo mapping the local contact to the GUID returned in the POST call. 
4.  The client should call to get a list of all the contacts on the server with their revision numbers from the web service using the Get Contacts web service method. 
5.  The client should then update each local contact to match the server:
  a.    If a file is on the server that is not locally known, assume a new contact was added, so a GET should be called to pull down that contact.
  b.    If a file is not on the server that was there previously in our sync repository, assume the contact was deleted, and delete the local contact.
  c.    If a file is both on the server and local but the revision number is higher on the server, then do a GET to update the local version.

Answer 2

Several tricks can facilitate the "synch-ing" of different "branches" of a database. However this type of thing is often laden with various business rules that may complicate the reconciliation process. If possible try and adhere to some of the following idea/principles, to make things easier: (It is assumed that the number of changes introduced in a given mobile database are small, relative to the overall db size)

1. All tables subject to have their records updated through this process should receive a timestamp column containing the date/time when the record was last touched.
2. The server version of the database is the "pilot version".
3. (preferably) Mobile versions are systematically created/updated by copying a full snapshot the current version on the Server. (in other words: synch-ing of mobile versions is not incremental, the mobile database gets overwritten with the complete pilot)
4. Keep a log of changes made on the mobile. Row-level, rather than column level is typically sufficient; the idea is to have a nice and short list of records to consider for CRUD on the server, rather than having to first create a "delta". The log can be a simple table with at a minimum: previous timestamp (important!), new timestamp (datetime when the record was manually changed on the mobile), PK of modified/added/deleted record, and "C, U, D" code.
5. You may need to keep a similar log on the Server side, if differential updates are required on the mobile instances (i.e. if #3 is not allowed)
6. Synch-ing up is done in two independent phases, first mobile-to-server, then server-to-mobile. These two phases do not need to be "synchronous",it is maybe ok to update mobile less frequently.
7. Provide a filtering/business-rules "hook", so that, even if at the moment, the plan is to systematically accept all CRUD operations from the mobile (with a better timestamp), it will be easy to introduce rules in the future.
8. By default, drive the acceptance of CRUD operations on a comparison of the timestamps. The "old" timestamp of the record is particularly important to detect possible collisions.
9. You may need to provide a collision resolution feature which allows someone to manually assist with these difficult cases. Alternatively it may be acceptable to lose some of the changes and apply a simple priority rule to automatically resolve such conflicts.

Answer 3

There are commercial products/frameworks that address this problem. Buy v Build is always a tricky decision, but at the very least you should understand what is commercially available.

[Apologies for the direct plug, but it's just so relevent, I think it's justified.] I work for IBM and so I happen to know about Lotus Expeditor, Java-basde product which includes lightweight local database, server-side replication capabilities, and loads of other stuff.

Answer 4

You use Lotus Notes. This is exactly what it is for. Handling replication conflicts is built in, as is encryption of locally stored information and information in transit over the wire.