Slashdot Mirror
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."
Modeling on a whiteboard is all well and good -- we did it at my job (and still do it) for all of our initial design.
The problem is, that whiteboard eventually needs to be erased. And when new people come on board drawing the same damn picture on the board over and over is tedious, not to mention error prone and bloody difficult for anything but high level representations.
We copied the whiteboard drawings to paper and then did some basic modeling in Dia. While OSS and Free, it's really not very good for this kind of thing and can't auto-generate mappings or anything like that.
I don't really have an answer here, but a data modeling tool is pretty essential unless you expect all of your developers, QA, management, and support to be around forever. You can get away without it, but you'll burn $4K in man hours pretty fast doing it by hand. I know we've burned considerably more than that and our diagrams still suck and are rapidly outdated. (We're in the process of buying tools... but I'm not in the loop by my own choice, except for the C++ debugger).
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.
:D
Dont really know about the rest, but in Oracle you just do a
create table foo (bar varchar2(10) default 'whatever')
Not quite sure, but this sounds like ANSI92 to me.
Of course, maintaint anything else than a 5 tables model by hand is a big PITA.
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).
Well, that's just plain wrong. Declarative referential integrity saves a LOAD of work, makes your data model self-documenting, easier to maintain, and believe me, most modern databases know have query optimizers that work wonders on them.
Of course, unless you charge by the hour, want to have to code on each of your 50 tables for such a basic thing as referential integrity, and want to make your database a pain to work with. If that's your objetive, you can also code primary key logic too
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.
It really doesn't slow things much, as long as you have the proper indexes, which you should have in any case, as usually foreign keys in a data model represent relationships you are going to use to join different tables...
This is, unless you are working with a very toy-like data model with 100 records on each table.
Just in case this isn't a deliberate troll to provoke database weenies:
/sarcasm.
If you don't have referential integrity enforced via foreign keys, how do you know your "accessor logic" is correct? Or more importantly, how do you know when it's wrong? Trust me, if you do not enforce referential integrity you can have subtle problems that do not manifest until after you have corrupted your data, long before you're at the "just doesn't work" stage. (Eg, child records with no parents). It is far, far more sensible to leave this to the DBMS to manage and optimise the rare cases where this might cause performance problems. I'm sure you're a god who gets this right in every single case without the DBMS' help, but we mortals are better off leaving it to Oracle (or Postgres, or whatever.)
And yeah, if you don't know what nulls are for, don't use them.