I am posting this question because I would like to know what the best development workflow is for someone who wants to manage all aspects of an online store.
As with all web development of course, having a live copy and at least one development copy of the entire software solution is of course very important. However managing Magento stuff is not like managing other "file-based" software because there is also a database component which comes into play, so, besides the fact that I can use a tool like Git as a VCS tool for source control, how would I go about managing differences in the database between the live and development versions?
I could of course make backups of the live database via cron, and insert the SQL INSERT statements from backup into source control, but after that two databases will evolve separately while customers register and place orders on one hand which go into the live database, and as updates are made to the development database separately. When it will come to merging development and live versions, the php files can be updated no problem via git (using gitignore on the single file which host database configuration details), but what about the database files? How can I merge the two files containing INSERT SQL statements from the two backups without causing a disaster and wrecking the system?
This is the shady area of the Magento development lifecycle which I am facing: managing database differences.
It seems to me, like the only solution to synchronizing the database contents which differ between the development/testing and live versions of the Magento store is to write down on a piece of paper all changes made in the development version via the Magento Admin Panel, and hope not to make any mistakes, and then once everything is tested and works file, to go over to the live version and carry out those exact same changes while Magento is taken offline and is put into maintenance mode. Since this is a manual process, is it prone to error.
So, what is the better way to handle database synchronization between the test magento server and the live magento server?