I am trying to update an established database system to leverage change management. The main point is to make the development and production environments follow the same process. I need help understanding how to group database elements into change sets, and which order to apply them in.
In my current design, change sets are grouped into roles, schemas, objects, and grants. objects is a catch-all for tables, indexes, sequences, views, etc.
Here is a sample directory layout:
roles/DATE-admin
roles/DATE-appA
roles/DATE-appB
schemas/DATE-schemaA
schemas/DATE-schemaB
objects/DATE-foo
objects/DATE-bar
grants/DATE
My current design applies any outstanding change set from roles, then schemas, then objects, then grants. The change sets which have not been applied from each group would be applied to the target database, and a helper script would be supplied to engineers to assist database setup. Liquibase tracks applied changes for me. (In production, roles would be managed via control panel, but they exist here for developer convenience.)
Is there a better grouping and order of operations that I can use to future-proof this design? Is this rigid order of operations reliably scriptable?
