Our company runs an e-commerce website with a database/platform designed (badly) from scratch. Because of this, we find ourselves changing the structure of the database, tables, columns etc fairly frequently to suit our evolving needs and knowledge.
To make development a bit easier, it's nice to have certain live data mapped across to our development database whenever we need it - selling prices, product variations, stock levels etc. However, identifying the required tables and fields for this data is not a "do it once then forget" solution, because we're moving the goalposts every time a table or field is added/deleted.
I currently handle this by creating a table of table names and field names (very meta!) where I basically list whether table_a and field_b needs to have its values pushed across. Or whether the basic structure of table_x needs to exist, but without data. I then have a script that deals with it all on a schedule.
The problem I have is that if a new table table_z gets created by a developer, he needs to be knowledgeable enough to know "is this required for the e-commerce side?", and then remember to add it to my table-of-tables so it gets picked up by my script. I want to eliminate human error as much as possible.
Is there a better way - maybe using MySQL's information_schema table or other metadata - to achieve what I want to do? Unfortunately, I'm already using column comments to give a human-readable name to each column for our CMS, otherwise that would have been the perfect solution. Is there a way to add my own custom metadata? How do you currently handle this replication in your jobs?
