How to properly manage a complex DB structure?

Question

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.

Answer 1

The question is quite vague, but I believe that LiquiBase (www.liquibase.org) was invented to solve exactly the sorts of problems you're describing.

Answer 2

While there are tools to check the differneces between two databases, these are dangerous to use to promote things to prod. Often there are objects not yet ready to be sent to prod, a tool would have no way to know this. This is even more likely to happen in a large enterprise type database used by several differnt applications.

Database code should be treated like any other code. It should be in source control in scripts and organized by release. Nothing goes to prod without a source controlled script for the proper release.