|Welcome to the Monastery|
Re: RDBMS 'Pretty' Schemaby htoug (Deacon)
|on Jan 07, 2003 at 13:20 UTC||Need Help??|
The 'nice' graphical tools become useless when you get to run on a real-world production database. The number of tables simply overwhelm them.
Eg. in our system we have 2199 tables with a total of 22698 columns.
What we have is a semiautomatic system, that allows us to extract information about the relevant tables and their interrelations from our datadictionary and have them drawn on our design tool (Rational Rose, but you can use almost anything that allows you to move the parts of the drawing around, add to it, display columns etc). Which tables are relevant depends on what you want to describe/document at the moment. You nearly always need to 'fiddle' with the automatic output to clarify it for your current needs.
We find that a datadictionary separate from the databases is extremely usefull. In the datadictionary you describe what you want your database(s) to look like, and then you have a tool to 'pat the databases into shape' without loosing data (which becomes important when you have approx 30GB of it).
The datadictionary can contain information that your current database system cannot support (at least not in the version released now), but which describes your intended use - eg. we had constrints in the dictionary years before our vendor supported them, and we have foreign keys dsescribed, that we definetely don't want implemented in the DB for performance reasons, but it is nice (and often necessary) to know that they are there - you could make a script that checks them at certain festive moments.
It also helps keeping development, test, release and production databases in step, and documenting your changes - you integrate your datadictionary with your code management system (CVS, Perforce or whatever), and suddenly you can remember what you did a year ago, and the wierd piece of code is much more understandable.
Once you have tried it, you can't understand how you lived without it for so long - just like your CMS!