There are multiple, ever-increasing number of sites that run off a single instance of a CMS (built in CakePHP) on a single server array. The CMS has a set database structure, but will likely need updating fairly regularly - new fields, changing names of fields, new tables ...etc.
I can't wrap my head around a good way to 1) keep the sites having their own databases (seemingly ideal), but two, always basing them off the same, regularly-updated database structure.
So far I thought to keep a single "template" database, and only update that, then write a script to compare and update - but even then, how would I know if a field changed, or if it was a new field, and previous field deleted...etc etc etc. I'm sure I haven't even thought of all the problems this will cause. Any advice greatly appreciated.
You could use a migrations tool like https://github.com/CakeDC/migrations. The basic idea behind migrations is to have a kind of version control for your database schema, with each migration representing a "commit" describing the changes (like add table, rename field, etc.) that should be applied to the database schema.