How to properly manage a complex DB structure?
- by errr
Let's say you have several systems using the same DB - each uses several schemes (sometimes same as the other). This structure of these schemes is somewhat very big and complicated.
Now, how could you possibly manage such scheme structure? Obviously using some sort of "configuration" - the simplest would be SQL scripts, but a more reasonable solution would be XMLs which can be easily converted into SQL, or some other readable solution (for example, JPA's XMLs or Annotations).
This solution though, causes a problem where you can't really tell if your configuration matches the structure of the DB schemes exactly. You can't say if those two are synchronized. Why wouldn't they? Well, in such big structure there are going to be many changes, and you won't always remember to save/commit your configuration after you've altered the schemes, or maybe you did save/commit it, but eventually didn't altered anything in the schemes and forgot to undo the changes to the configuration.
More than that, another problem (not caused by the configuration, but isn't addressed by it either) is versioning. I don't see any good way of managing the DB schemes versions (say our last alteration makes 3 systems crash - not good, how to "rollback"?).
And thoughts? thx.
