>>One approach is to not bother updating a web-site based DB unless the data is actually requested. Using your pricing example:
>>Record the DateTime the price of an item was last updated on the web server DB. When that item is next accessed check whether the update is recent enough and, if not, refresh from the main server.
>
>Yes, this is the one I thought about. We need to have an application at the client site which will upload to a Web Service all the updated records. It is sure that we cannot change the data model locally. So, we can only act upon. So, everytime a synchronization will be needed, I see that the local application will get all the primary keys from the Web Service with the last updated time stamps. Then, the local application can verify that with each record locally and synchronize new ones. This will also include another method to add new ones and another method to delete old ones.

So far it sounds like you have:

- a proprietary database in a store: call it the Local, Proprietary database (LP)
- an "open" database (maybe SQL Server?) on a Web server: call it the Web, Open database (WO)
- a "sync" program that updates the WO database from the LP database

You could consider implementing a third database - a Local, Open (LO) database at the store. For example, set up a copy of SQL Server at the store, which has a similar (or the same) design as the WO database. Then what you could do is:

- the "sync" program updates the LO database (instead of the WO database) from the LP database
- you can then use SQL Server replication to replicate the LO database to the WO database

I imagine MS has put a lot of effort into making SQL Server replication reliable, robust and efficient. This would save you re-inventing that wheel.

When you're syncing the LP database with the WO (or LO) database you basically have to match CRUD operations that occurred on the LP database. As Viv mentions, create and delete operations may not occur that often, so you may be dealing mainly with updates. One technique that can ease processing of updates is for the sync process to calculate a hash of the 17 or so columns you need and store that value. You then only need to update rows in the WO or LO database where the hash value has changed. Timestamps can sometimes work for this purpose, but some applications change row timestamps unnecessarily e.g. if someone edits and saves a row without actually making any changes.