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."

Re:Some make pretty pictures...

[Zathrus]

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 [lysator.liu.se]. While OSS and Free, it's really not very good for t

All night programming sessions

[theMerovingian]

I somtimes mark up a whiteboard, and take a digital picture before erasing it.

Never know when you are whacking a good idea...

Re:All night programming sessions

[Zathrus]

Well, we now have one "electronic" whiteboard that will happily spit out anything written on it to a PC or print it on paper, but it's (of course) in a back boardroom that's not at all useful to developers (hopefully management is getting some use out of it).

Most of our design sessions are done on the 4'x6' whiteboard in my cube (yes, I have an astoundingly large cube; there's one other person in it, but it could house 4 people very comfortably). A digital camera would be a good start. I think it was even

Re:All night programming sessions

[theMerovingian]

Do you have a URL for the electronic whiteboard you purchased? Does it work as advertised?

Data Architect by The Kompany

[hotgazpacho]

http://www.thekompany.com/products/dataarchitect/

$60 USD for the download version. I used it for my last Database design project (first on MySQL, then moving that model to Postgres), and it was great. Made my life a lot easier. Available on Linux, Windows, and OS X.

Some options..

[maunleon]

I assume that you are working with relational databases, although you don't mention it.. (data modeling could mean a lot of things)

Some DBMSs have some decent, albeit limited graphical design tools (MSSQL, Access..)

together [borland.com] from borland does entity relationship diagramming, and so much more. (great tool)

I believe IBM's rational rose [rational.com] may also do it.

Since my job includes SQL design as a subset of my duties (i'm a developer/architect, not a DBA) these two tools are significantly more valuable than ERWin.

I just wish I could get my boss to buy them. :)

dia & tedia2sql

[szap]

We have been using dia [lysator.liu.se] and tedia2sql [tigris.org] for many of our projects. This includes a project involving about a hundred tables, with foriegn keys all over, and a number of user defined functions (aka Stored Procedures) and aggregate functions.

How it's done is, roughly, for a few related tables:

1. Create tables using dia's UML's classes.
2. Join the tables using UML Association to establish foreign keys.
3. Set dia to autofit diagram into a number of pages.
4. Add comments, draw pictures, whatever dia can draw.
5. Save as an uncompressed XML file, chuck it into CVS. CVS likes text, so don't compress it.
6. Optional: Print out pretty diagrams to printer. Dia's autofit is nice.

Repeat above if you can't fit your tables into a set of nice looking pages.

1. Create a Makefile to convert .dia to .sql using tedia2sql, and .dia to .eps using dia. (Left as an excerise to the reader). Bonus: autogen .eps to .pdf.
2. Set up tedia2sql config to generate to your favourite rdbms. Or change Makefile to generate SQLs for each of Oracle, PostgreSQL, MySQL.
3. Type 'make'.

1. 5. Profit!

Congrats! You have have fully printable, documentated, usable SQLs, and have version control on the schemas too. (Missing step is "4. Debug")

P.S. tedia2sql is written in easily hackable perl.

Visio

[Ty_Webb]

Been using Visio for quite some time now.

Negatives:
-Fairly resource-intensive program
-Takes time to find all the features and figure out all the tricks and shortcuts to operating it
-An abundance of features

Positives:
-Allows for creativity in customizing tools/settings/functions for personal use
-Covers many business modelling themes/motifs
-An abundance of features

Re:random rants

[1001011010110101]

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.

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 :D

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.

Re:random rants

[bmac]

The default value thing I was mentioning had to do with altering a table that has data already in it. Giving the new fields in the existing records a default value has not been possible in the envs that I've worked in. This is related to another problem with adding a column after table creation, namely, that the column must be able to hold nulls, which screws up query logic, as I've found over and over.

And, as you say, most foreign key relationships are for indexed fields or groups of fields, which to my

Re:random rants

[1001011010110101]

The default value thing I was mentioning had to do with altering a table that has data already in it.

Again, in Oracle:

alter table foo (add column bar varchar2(50) default 10)

The rule is, the columns you add must be either nullable or have a default. Check the manual, you might be amused on the things you can do.

I *do* perform the primary key checks before I ever issue an insert. Why?
I don't like to parse db error message strings, so I perform N+2 select queries beforehand to ensure that if the inser

Re:random rants

[bmac]

I saw all your comments in this thread and you obvioulsy think too much of yourself. You still have to learn how fully exploit a *real* DBMS.

Hmm, I know what has worked for me and I *know* that my apps *never* have bugs. And that is no hyperbole. And I know that no one has *ever* written a book on how to write a bug-free app of any significant complexity. I'd love to read it, though, if/when it happens.

And, BTW, I'd really rather not come across arrogant or cocky, but text is a bad way to represent in

Re:random rants

[judd]

Just in case this isn't a deliberate troll to provoke database weenies:

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. /sarcasm.

Re:random rants

[1001011010110101]

For instance, on a string field, why not just use ""?

In fact, in Oracle last time I checked "" IS null
Odd, eh?

Re:random rants

[bmac]

Though I'm not a statistician, I have done for-pay work (may years ago) using SAS and SPSS to help people analyze their data. As a result, I can tell you that NULL is used as a label for "data not present", which is used in certain stat algorithms to determine, as I understand it, a) the probability that your conclusions/results are meaningful, and b) that you have enough data to draw conclusions.

If, for example, your varchar(n) field type didn't have the ability to store a null, you would have to specify

Re:random rants

[bmac]

I've found that subtle problems are the result of not properly designing, then incrementally building with iterim testing. And, it certainly does not take a "god" to make bug-free software, but I don't tolerate bugs in my software, because that causes my employers to have to call me, and I'd rather let them have their business logic as they want it and leave me to enjoy life.

More specifically, do you not design your software such that all your business logic for a particular table is located in the same p

Re:random rants

[GoofyBoy]

>More specifically, do you not design your software such that all your business logic for a particular table is located in the same place?

Reasons why you still need FKs.

1. Programmers make mistakes. If they were perfect you wouldn't need a whole lot of things besides FKs. But people are human. Having a data architect and a programmer work on relations is better. (two heads are better than one?) Getting messed up data is one of the worst things you can do to the reliablity of an application. You c

Re:random rants

[bmac]

1. Programmers make mistakes. If they were perfect you wouldn't need a whole lot of things besides FKs. But people are human. Having a data architect and a programmer work on relations is better. (two heads are better than one?) Getting messed up data is one of the worst things you can do to the reliablity of an application. You can fix a display problem or a "missing button" functionality but once that crappy data is in, its a pain to fix it, if you can.

Sure, we all make mistakes, but the really good pro

Re:random rants

[GoofyBoy]

First;

>we all make mistakes

Then you have these statements that assume that programmers do not make mistakes;

>good programmers find their mistakes and fix them

and

>if my query is doing joins between indexed keys, and I've ordered my

and

>why can't all the middleware be implemented using constraints?

People make mistakes. They happen. Thats why software development is evolving. Thats is why its good to have checks and double checks. Especially at very little cost.

> IMO it is still just

Re:random rants

[bmac]

Not strictly. Say you have a sales/accounting which feeds data, then you have budgeting people which needs to forcast. Say that they have two different IT departments. One uses an internal one, the other thinks that the internal is crappy and outsources. They can develop two different applications/design doc/programmer teams/end-user requirements, using the same database.

You can *not* have two different design specs implemented that *modify* the same data. Read-only apps don't count. There would be no c

Re:random rants

[GoofyBoy]

>You can *not* have two different design specs implemented that *modify* the same data.

I totally agree with you, but I've just went through this situation and when the client is motivated you can't just throw out technical/"because its not clean" reasons. There are lots of things which should not happen but they do.

>how do you set up a FK relationship between a table that has a field that, depending on another integer field, can be a link to any of N other tables? Is there a standard for this?

I th

Re:random rants

[bmac]

Just because a technology does not fully captures a requirement doesn't mean that you should avoid it entirely. You really are doing a dis-service to your client since you are not using full use of the tools they bought/implemented.

Actually, my clients are always *totally* satisfied with the results of my work, and no client I have ever worked for has given two sh*ts how I got the job done, so long as the programs worked. There are *never* any bugs in my finished product. I know it sounds ridiculous, bu

Erm, you're a DBA?

[abulafia]

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

I hope you're trolling, and not actually deploying software.

This statement is analogous to "I have to say, I've never seen the use of backups to be needed on any filesystem I've worked with."

And unless you're doing hardcore statistical analysis, *never* allow nulls.

What the hell are you talking about? Quick - what's your employer's mother's sister's middle name? Hm, maybe that's

Re:Erm, you're a DBA?

[bmac]

I hope you're trolling, and not actually deploying software.

Sorry to disappoint, but I do, and it may just happen that you end up using my software on *your* machine, someday, God Willing :-)

This statement is analogous to "I have to say, I've never seen the use of backups to be needed on any filesystem I've worked with."

Though I'm a huge fan of hyperbole, that is an absurd analogy.

What the hell are you talking about? Quick - what's your employer's mother's sister's middle name? Hm, maybe that's a nu

Re:Erm, you're a DBA?

[GoofyBoy]

>I won't even begin to get in to the evil I've seen applications do to work around stupid NOT NULL declarations...

>but I will say I administer multiple complex databases for multiple clients under postgres, mysql, and oracle.

Um... You might want to pick up an db optimizer book to see why nulls in general are evil.

Nulls are funky beasts. They don't represent anything, including other Nulls themselves. This is as far as SQL standards define them and leave the ugly details to the vendors. What this

Re:random rants

[dubl-u]

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.

Well, there are two ways of looking at the typical system. Take a web application as an example: Either a) a database is where good objects go to rest when you don't need them, or b) your code is basically an engine for transforming HTTP requests into SQL statements, and then transforming SQL result sets into HTML.

I think you and I are in the

Re:random rants

[bmac]

Well, there are two ways of looking at the typical system.

I don't see it in either of the ways you mention. I see software, regardless of architecture, as made up of three interconnected realms:

1. Spacial

This is the layout of your data types. In C/OO, it's your structs, class data members, representation for enums, etc. In a RDBMS env, it's your table schemas.

2. Temporal

This is the collection of routines that modify your data structures. In an C/OO env, it's your library fcts and class member fc

Re:random rants

[dubl-u]

I see software, regardless of architecture, as made up of three interconnected realms.

Interesting perspective.

And Prevaler looks to me to be just a memory RDBMS system, but I disagree with them that the db does *not* have to be stopped to dump the memory image to persistent storage. I feel that in some db apps, you would get bad results occasionally from some race conditions, which would be very difficult to track down.

I'm not sure what part of their literature you're referring to, but they can do wha

Re:random rants

[dubl-u]

This means that referential integrity in the database serves more than to make up for bad middleware, it means that this is where business rules and other items that rarely change can be maintained. [...] Another thing that people often overlook is the fact that many times a database serves more than one application.

I don't overlook that; I actively repudiate that.

One of the fundamental notions of object-oriented programming is that is an object is data plus behavior. If you have several different chunk

From the Oracle camp

[1001011010110101]

I work for an Oracle partner company, and we tend to use the Oracle stack. I've found Oracle Designer to be quite adecuate for the task. We use it both for logical and physical data model, and for the script generation. In fact, we also use the tool for frontend generation in some cases. It also has some pretty good code repository tools, with versioning, multibranches, dependency and impact analysis and so forth.
A bit quirky, not to cheap (its part of a bundle of dev tools) but quite a nice thing for Oracl

From the MS side of the fence...

[shadowxtc]

I used to love Rational Rose... and nowadays my bloat of choice is indeed Visio. I can't say much about either as I'm a lowly developer and rarely stress them, but Visio has proved useful in some more obscure tasks now and then...

List what you need, please.

[Randolpho]

Perhaps it might have been better to list what feature's you're looking for in a data modeler, that way we can point you in the right direction. Still, I'll blather on about my fave...

I use DBDesigner4 [fabforce.net]. It's free/GPL, so the price is at least a feature you're looking for. It's also available on Windows, & KDE/Gnome (not sure what widget toolkit it uses), so that's a plus (or minus depending on your religion ;)).

It's also fully optimized for MySQL if that's your platform, but it can support any ODBC database, Oracle, or MSSQL. It has a very intuitive (and pretty, IMO) graphical interface, with great, easy-to-use tools for visualizing, grouping, and relating your data. It also features a graphical Query Builder that lets you point/click your way through complex queries, returning SQL you can insert into your code. It allows reverse engineering of any database it can connect to, as well as synchronization so you don't have to do the setup work after you model, like you might with, say, Visio.

It's not UML; it's really designed specifically *for* relational databases, so if you're just looking for a UML data diagram builder for internal data representation, this is probably not the tool for you.

But if you are using a relational database, I don't think you can go wrong with it. It's at least worth a download.

Experience speaking

[borgboy]

If you really really need ERWin, you cannot afford not to have it. But I dont think most people need it, and CA aint exactly the most customer centric company out there.

ER/Studio is a close second on functionality, and wins IMO on value delivered and ease of use. It does quite a lot.

Visio...might seem to work ok at first, but will likely end up pissing you off. I came to this conclusion for both it's data modeling capabilities and the rather hamstrung UML additions.

I've used ERWin and DeZign...

[anthony_dipierro]

And personally I find them to be about as useful as a pen and paper, and much more expensive. Of course, I felt the same way about our DBA (she was about as useful as a pen and paper, and much more expensive).

Builder.com Readers' Choice

[superyooser]

See the 2nd Annual Builder Readers' Choice Awards [com.com].

The results for Best Modeling Tool (scroll to last chart) are:

• Winner: Visio 2003 [microsoft.com] - 37%
• 1st Runner up: Rational Rose [rational.com] - 18%
• 2nd Runner up: Oracle9i/10g JDeveloper [oracle.com] - 7%

Eight other products are on the chart.

DeZign Thoughts

[sidespace]

I used DeZign at a contract position this summer and have used Visio Professional in the past. I would not recommend DeZign unless your budget is very tight; it was much less featured than Visio Professional (and couldn't compare to Visio for Enterprise Architects).

The question always boils down to: what is your budget and what is your target market? If you are in the sub $500 market I would recommend Visio Professional as the tool has several other uses besides plain data modelling. Likewise, if your cu

Another vote for ERWin

[Future Shock]

If you are SERIOUS about your data modeling, ERWin is probably the best way to go. Many professional DBAs probably couldn't do their jobs without it. I would also consider Rational Rose to be on a par with ERWin, but it's ownership by IBM has rendered it "partisian" in some DBAs minds, as is Oracle's product. Erwin has the advantage of not being owned by any company that sells a database, generates code for all of them equally, and is mostly transparent across all of them, especially when transitioning between logical and physical designs. This especially makes it a favorite of consulting companies that have to move from client to client...ERWin also has an excellent tutorial on HOW to do data modeling, which can at least serve as an entry point for someone new to it's practice

On a side note, ERWin is NOT as object model-centric as Rose and some others, as it is old enough to have been developed before object modeling became cool. But that is a minor quibble.

I also find a good set of 3x5 filecards (taped up to a whiteboard or large construction paper) an excellent starting point for my models, particularly when trying to model those main logical entities that end up driving the entire design. They have the advantage over whiteboards of being at least partially on paper should someone erase the board...

Re:Another vote for ERWin

[dubl-u]

I also find a good set of 3x5 filecards (taped up to a whiteboard or large construction paper) an excellent starting point for my models, particularly when trying to model those main logical entities that end up driving the entire design.

Agreed! Index cards are a highly underrated technology. They're durable, easily manipulated, have a great UI, and can be used collaboratively. Plus, they don't cost $4k per user.

They have the advantage over whiteboards of being at least partially on paper should someone

wide range of tools means define your requirements

[big!theory]

you need to narrow the field a bit by defining your requirements. do you want uml/high level analysis tools for people who only do analysis and never write code? or do you want something that lives closer to the actual end result, presumably a relational database?

i assume the later (because i have nothing to say about uml tools). if you do serious design work for medium complexity apps, you can immediately eliminate drawing tools like visio and toys like access.

visio is simply a clip art tool for dia

Sybase PowerDesigner not bad

[smoon]

I've used Sybase Power Designer [sybase.com] (an older version) before. It's not too bad and a lot cheaper then erwin. Works with lots of database servers, and prints purty wall-sized pictures assuming you've got a plotter or don't mind pasting together a bunch of sheets. Also generates SQL for building/altering databases -- typical stuff.

The only thing to look out for is which 'version' you get -- this is one of those products where you pay extra for specific features, so you'll pay anywhere from $800 or so to over $3

how well does it import/export DDL from X database

[1iar_parad0x]

ONE VERY IMPORTANT ISSUE: How well does X software import DDL from Y database and generate DDL for Z database. This is important. This shouldn't be an issue but sometimes it can be. If you have to import data from an Excel spreadsheet, existing database (like mSQL, MySQL), or some exotic ODBC datasource, can X software handle it. Frankly, that's something a professional magazine has to test. That's were downloading said software in trial mode (should be fully-functional) becomes really important. T

Draw things on paper, then...

[Alex Belits]

...make a pretty diagram in Xfig.

Yes, Xfig. It will let you place all the arrows and boxes with names, but it will never pretend that it understand what you are doing, and you will still have to understand your data structures, as you should.

data modeling tools

[sqlgeek]

Well, if you're designing a model that needs to be implemented across several databases (rather rare) then I'd go with ERwin. It's the tool of choice for consultants who work with many databases -- say data warehouse folk.

On the other hand, if you're working with Oracle you really want to go with Designer. It's not being terribly actively maintained, and will likely be replaced at some later point by JDeveloper, but at this point it is unsurpassed for functionality. You want to generate audit columns (c