Please reply to all when replying on the list...
On 7/10/06, Kevin Bednar <kevin@stockwelldesigngroup.com > wrote: Thanks Aron. What I'm actually trying to do is this:
Postgress in physical store, being used by POS system as the back end.
MS-SQL being used on web server by ecommerce system.
Table structures are different of course, but some common fields. What I want to do is when an item is sold in the store, update the quantity field for that sku number on the web site and vice versa. Only 2 fields basically need to be updated on each side, the SKU number and quantity. This is to keep the product table in sync and try to avoid selling product that isnt in stock and setting a flag on the web system stating such. Thanks for your help.
For something this simple you are probably better off doing some custom coding.
If you have the ability to modify the databases, I would recommend putting a trigger on each database so when there is a product sold, that sale is recorded in a temp table (which serves as a queue of data that needs to be synched). Then have a process read from these temp tables and feed the data back to the other database. Of course, I am assuming you have full control to change the databases - some vendors do not allow that.
You may be able to connect the databases - MS SQL Server will definitely allow you to connect via ODBC to another database and feed data back and forth. I think there are add on modules for PostgreSQL but I have not tried to have PostgreSQL talk to other databases before.
-Aaron
