The merchant run his major database on a windows platform, this will manage the his retail in shop sales, the wholesale in shop sales, the order, storage, account etc. That is on windows server, microsoft sql server, and the application was written by c++ codes, hosted in the company central office.
But he would have a separate online web database to do the online sales on LAMP which is hosted on a apache server in the ISP data center.
The issues we have now is to syndicate some data between two database, online web database on LAMP and major database on microsoft sql server etc.
Two major requirements
1) when online database makes the sales, and the sale online transaction is done. Major database would get how many items are sold and update its data of the items in stock.
2) when major database change the price, it should also change the online web database price.
Three approaches
1) these syndication are done instantly.
when online sales made, the items in stock on the major database would change instantly. same, when major database changes the products price, online database product price would change instantly.
To do that, for online database product price change with major database, I think The merchant can program it into its major database windows interface, when price is changed on major database through their windows application, it would also remote access online database (mysql) and update the price. Am I right?
For major database change the items in stock when online sales are made. I think the major database has to have some listen channel open, online database would send the data to that channel (xml format or http post etc.) when sales made, right?
This will also require some approaches to make sure the temporary internet interruption, hackers interruptions would be detected and handled.
2) these syndication would not be done instantly but be done every day by auto schedule ftp task and auto schedule update tasks.
By end of every day.
All the price changed on the major database would be saved in an xml file on the major server. All the sales made online would be saved in an xml file too on the web server.
Schedule an auto ftp task to exchange these xml files between two servers every day.
And then schedule the update task on each server to update the items in stock in major database and update the price in online web database.
This way, we only need to detect if the scheduled ftp is done or not at the schedule time, instead of the approach 1) we have to be on alert all day.
3) still by end of the day saved the changes on each database in xml. but instead of auto schedule ftp and update, let human involved to make sure these update works are done right.
Such as the price would not be changed so often. And when it is changed. You would want to make sure that there was a human double checking it was done and it was done right. We can still schedule the ftp of xmls, but update database with these xmls, it would better to have a human to activate the task and see the results after the task is done.
This is a small to middle size business, the price would not change very often, so instead of let the two database auto change the price, I think a little bit human involved, specially in the price change part, should be worth it. Better safe than sorry.
