a view into the sordid life i lead

Tuesday, July 31, 2007

ER diagrams

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:

There there are the Free/Open Source tools that I think have some merit. Here are my findings of all the tools out there that I ran across:

Full-blown GUI'ed applications:
  • Mogwai-ER
  • 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
  • DBVisualizer
Scripts that produced static images (png, jpg, etc.):
  • GraphViz::DBI
Scripts that produce editable images (XML or something else that some other program can handle):
  • sql2dia
  • 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.

Labels: , ,