To get this out first thing: I am not a Magento developer. Don't know much about it. I just consult when it comes to server and project management issues. I'll occasionally get my hands dirty with a rough patch for issues, but I don't know anything about the "Magento Way" of doing things.
We have a Magento site (126.96.36.199) we perform active development on. We have a production server and a development server.
Magento (like WordPress) is very database-dependent. We can version and transfer code without issue. We need an easy and reliable method for merging the databases, however.
On production we want to retain products, orders, reviews, site configurations (such as URL), etc; and on develop we want to retain CMS, plugin configurations (especially troublesome when we install a new plugin), etc.
Currently, I manually backup both databses, trim out the table I don't want from each, and combine them. I then push the resulting Frankensteinian database to production. This is as error prone as you can imagine.
There has to be a better way to do this.
I've toyed with the idea of a shared database, but that isn't ideal, as unapproved changes to products and pages would reflect on the production site.
I've also toyed with the idea of piping the database dumps into Git, but that still requires me to know which tables to keep from each respective database.
Now that I think about it, would the better path here be to have a second "staging" store on the production environment rather than a separate development server? Caveats?