Sep 02, 2008
Data models are good and they are clear, if you're the person writing the application and devising the model. Hell, sometimes, they are not clear even then! So, imagine what happens when you get someone from the street to connect to your database and read your schema in order to understand it. No chance!
Now, this is not about some poor wardriver who doesn't know how to read the implicit relationships between tables in your model - they had it coming! But what about your legit users, working on a particular aspect of your infrastructure or application, such as developers, DBAs etc. ? How on earth do they make sense of it all when they first start?
Yes, yes, in an ideal world everything's properly documented, but when was the last time you saw that in a real life situation? Real IT people don't write helpful comments when they create their tables, views, functions etc. Referential integrity? Don't make me laugh! Most developers avoid database constraints, to keep the application portable between database systems and database error messages to a bare minimum. Integrity rules are usually enforced at the application level. From a DBA's perspective, most enterprise-level databases are big collections of seemingly unrelated tables, with no business logic in the DB system itself.
But don't despair! Help is at hand. Enter Schema Spy:
You dowload the jar file, and then you run something along the lines of
java -jar schemaSpy_3.1.1.jar -t pgsql -cp /path/to/jdbc.jar \
-u user -p password -s schema \
-db dbname -host localhost:5432 \
After a while, you have a look in output-dir, and the reports are really nice.
Schema Spy even deduces table relationships from field names and types. And it seems to support several different database systems, including Oracle and MySQL. Hurrah!