Ted’s Rants and Raves by Ted M. Young

Here at work, for documentation purposes, we want to be able to reverse-engineer the database schema and get a pretty diagram. Since we generate the schema from some XML files, we can’t generate a model from there, so we have to go to the database. Personally, I’d like to generate it straight from the XML, but that’d require some custom work using GraphViz or maybe yWorks. Anyway, one would think that getting a tool to print a pretty diagram of the schema is pretty easy.

This wouldn’t be a blog entry if it was easy…in fact, it’s surprisingly hard to get a good reverse-engineered diagram from any tool, even those costing $4,000.

Hey ERwin, 1993 just called and they want their modeling tool back!

Seriously, I expect a $4,000 tool to be able to do everything and when it comes to reverse engineering, I expect to be able to point it at a database and have it do its magic. Would you believe that CA’s AllFusion ERwin Data Modeler (ugh, what a name) doesn’t know how to speak directly to a SQL Server 2005 database? Oh sure, it gives you that choice when reverse engineering a database, but there doesn’t seem to be any place to set things like username, password, database name, etc. So what happens is I get: "Unable to locate client connectivity software. Check with your database administrator to install the appropriate client software." Well that doesn’t help, since SQL Server 2005 is running on my machine, and on my machine I am the administrator and I have no idea what they’re talking about. If you’re going to display an error message like this, you had better supply a link to a help file that explains what’s wrong and how to fix it. This isn’t some rarely seen database like Paradox, this is Microsoft SQL Server 2005.

I’m betting that a lot of people use database reverse-engineering tools since database schemas are usually the most un[der]documented part of a system. I would expect a $4,000 tool (or even a $400 one) to provide a solid wizard interface for setting up connections to database. If they don’t want to "dumb down" the product with a wizard, then detailed error messages would be fine. Error messages such as "Error 0" are inexcusable in commercial software, let alone ones that are expensive and have been around for so many years. I could understand a 1.0 product having limited help or cryptic error messages, but not a product that’s at version 7.1.2.

Since I couldn’t get the direct-to-SQL Server 2005 connection working, I figured I’d try the ODBC connection and that got me connected to my database. ERwin was finally able to reverse-engineer the tables, though not after some very tedious selecting and unselecting the tables that I wanted. ERwin (and Visio 2007 as well) makes this so hard because if I want to select, say, 100 tables out of a possible 250 tables to reverse engineer, I have to select and click a button for every single table. I can’t multiple-select, or drag-n-drop, or even use a search expression (let alone a regexp).

With the tables and their relationships imported into ERwin, I find that its line layout algorithms are awful (though not nearly as bad as Visual Paradigm’s VPSuite’s routing algorithms, which seem to be non-existent): with all of the crossed lines and dashed line styles, it’s hard to see what connects to what. At least the number of lines crossing the entity shapes are kept to a minimum.

With all of the fancy algorithms that have been around for years (many, many years) to lay out lines with the minimum number of crossings, I guess it’s just another one of those things that few people need. My customized XML to Diagram solution is looking better and better.