>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.

Thanks, but replication cannot apply here.

The main database is from Level Extreme World, a large scale development project targeted at many businesses. Thus, the primary keys in it and so on are managed from the server side. Also, on the client side, they have this store item image encrypted in a field which complicates things up. Also, from my understanding, the only way to have this real time inventory issue, that we are facing, when someone purchases an item, would be to have a Web Servier at the client site. It is my understanding that online stores that are selling their entire POS system online have their Web server onsite, and that is connected to the POS database.

So, basically, installing a local module to synchronize things up at a specific interval, with a drop of the real time inventory issue, might apply.