Synchronized Online/Offline data applications, part 2: Syncing large tables and tables with foreign relations

Author: Peter Aron Zentai February 22nd, 2013

[This document is in the process of creation]

This post is a part of a series, find more here:

I have recently created a sample app that synchronizes tables from a Northwind database (published with WCF Data Services) to a local webSql or IndexedDB cache. Creating a nearly identical local copy of the remote data potentially with foreign keys and large number of rows faces some challenges.

First of all, we need a scalable way that is able to copy any number of rows over the net – in chunks. Getting all the items at once would definitely do no good to us, this approach is really everything but feasible beyond of couple of hundred lines. Secondly we have to deal with dependencies – in our case we are to sync three tables: Products, Orders and OrderDetails. The OrderDetail table has a composed primary key that consist of two foreign keys referencing Orders and Products.

Example Project

You can download the VS2012 solution from here. Index.html and Scripts/localdb.js contains all the relevant client code.


The online source of data will be an OData endpoint exposing a partial of the Northwind database. On the client we will use JayData and its OData and local providers to connect to the two data sources and copy data from one to the other. Since we are to store only a subset of the fields locally (the ones for example that take part in creating a fast auto complete solution for a product name) we will need to create local type definitions for the Product, Order and OrderDetails entities. The same thing will be automatic for the OData endpoint.

Efficient copy of entities

For an efficient paging based copy procedure we will use a continuation algorithm that repeats until the last page is copied – only taking a number of items at a time.

The trick is in the syncSet method.

fromSet .take(pageSize) .toArray()