Recommended Data Modeling Tools?

dnxthx asks: "After performing a fairly comprehensive web search (including Slashdot) I came to the (possibly incorrect) conclusion that there were no high-visibility sites that comprehensively reviewed and compared data modeling tools such as ER/Studio, ERWin, DeZign for Databases, System Architect, or Visio for Enterprise Architects. Since some of these tools can be quite expensive (ERWin is about $4K US it seems), I was wondering what the Slashdot community's experiences were with data modeling tools such as these, or some that our group has overlooked."

Comments

random rants

[bmac]

First, Visio is *out*. Basically, it just plain sucks. All I've found it useful for is *very* rudimentary diagrams. It's so slow and cumbersome that I would never chance depending on it for a live db schema. I really wouldn't want to use it for even creating parallel docs for an existing app, it's so bad. (BTW, I have .NET for Enterprise Architects, which includes the newest Visio. Sidebar: VS.NET is great for prototyping but I wouldn't trust my reputation on an app created with it -- it's my bootstrapper.)

Second, after years upon years of experience, I've come to the conclusion that the most effective technique to use is to have a script that creates and populates the db. Tie this in with functions that do backup/restore to/from text files and you can recreate the db (schema *and* data) from any save point from pure init forward. The other advantage to this is that I despise the 'alter table' command; I find it much easier to just drop the table and then re-create and re-populate it. One key point here is that most dbs that I've dealt with (Informix, MySQL, SQL Server & Oracle) do not let you specify the default value for a new column automatically. That means you're gonna have to script the modifications to insert/compute the new column anyway. And, forget about modifying a column type, for extremely non-trivial changes it is nearly impossible (the exception being the changing of a char's or varchar's length).

And also, as a mini-rant, I have to say that the use of foreign keys has *never* been necessary in any db I've designed (and I have created very complex db apps with 30-70 well-connected tables). My point here is that your accessor logic (so-called middleware) is what manifests the "foreign" relationship and if that's screwed then your app just doesn't work. Slowing the db down to do all that referential integrity is a waste of time. You've either got the middleware right or not.

And unless you're doing hardcore statistical analysis, *never* allow nulls. If you allow nulls in *any* field, your query logic (read: the app logic itself) must be an order of mag more complex to deal with results that have nulls in their field values. Blech! Instead, disallow nulls and be careful to initialize your records properly.

Good luck.

Peace & Blessings,
bmac

For true peace & happiness: www.mihr.com