Some background here, question start in bold below. I hope this is the right place. I am not looking for a code snippet but rather insight on the best way to handle this situation.
I currently develop a proprietary site for my work that allows for back office quoting and management of said quotes. It is written in PHP using PDO(mysql).
We are looking to replace quickbooks POS with OpenSourcePOS. I have already made a fork and modified it for our use.
The last point in this system is the accounting office, running Quickbooks Desktop. I have successfully set up CData ODBC Driver and connected to it from OpenSourcePOS using php/odbc_connect
So I know I can connect and read/write to the QB database with PHP. I can figure out the table relations etc. What my question is:
what is the best way to actually integrate the databases? I am thinking I should have a mirror of the quickbooks database that gets updated every night so that for most read queries I don't have to hit the live db as people are using the software.
and then obviously for the db writes, I can't do those during work hours either for the same reason. do i have a queue? or do i just analyze the POS/quote dbs for new entries, sync, and mark as synced?
I know I can figure out a solution, but I want it to be the right one as this system will be very important. sorry for all the text. thank you.
