When you're given a database that needs to be migrated or dealt with in any way, the most useful first step is to be able to create the 64 mile view: create a diagram of all the database entities and see how they map to each other. Then figure out how they map to the target.
This is all very nice when you're dealing with 10 tables. WHen the number exceeds 100, you'd rather have a tool to do the diagramming for you. This is what has occupied the last few days of my life.
Given my propensity for Free/Open Source solutions I wanted to see if this was possible without succumbing to the world of proprietary software. Yes, I know that Visio provides what I need, but there are annoyances about Visio which I will document in the Proprietary section below. Just read on if you care. Anyway, this is primarily a place for me to document my findings so fooie.
Basically what I needed was a way to generate an editable diagram of all my database elements (mainly tables, keys, indices, relationships) by reverse engineering an existing table, then manipulate this image and add any new relationships or maps that I wanted to. Standard ER diagramming stuff which Visio does beautifully. So, on to what the solutions are.
First, there are lists like this that have been compiled by others, so I'm going to start there:
- Eagle-USB Wiki Modelization Tools
Full-blown GUI'ed applications:
- DBDesigner4: obsoleted by MySQL Workbench. Does not play well with recent versions of MySQL (>4)
- MySQL Workbench: has lots of potential but is very annoying to use right now
- DIA: has no reverse-engineering capabilities
- DataArchitect by TheKompany
- Toad Data Modeler
- schemaspy: creates an amazingly useful data dictionary with full references. Seems to be primarily useful on Windows for now, since the database drivers don't seem to like the jdbc drivers I have on my Ubuntu box. Maybe just a configuration issue. I'll add more as I work with it. The Drupal Schema map is a very good (and useful) example.