What is the best CASE tool for database development and why?

Question

I know there are a large number of CASE tools out there, some better than others, but what are the ones used by those in the field, and why do you use those specific tools?

In this case, I am just interested in CASE tools for database design, and more specifically, ones that will let me "draw" the schema diagram, and then will create that schema in the database, as well as take an existing schema and create a diagram

Some things to consider might be:

• Cost of the tool
• Can it deal with multiple vendors (eg. MS SQL Server, MySQL, Oracle, PostgreSQL)
• Ease of use

So, what do you use and why?

Answer 1

Hands down the best data modeling tool is ERWin from CA.

It's capable of quite a bit - logical and physical diagrams, multi-dimensional models, full forward and reverse engineering (i.e. generating a database from a diagram, and generating a diagram from a database). It can do more advanced things too, like data placement, volumetrics, etc.

For diagramming it supports things like subject areas and different notations (crow's feet and IDEFX1, for example).

It supports all major vendor RDBMS.

It's pretty expensive for all that - last time I went to check it was $4000.

It definitely has its quirks and idiosyncrasies, and a learning curve, but that's to be expected with any tool with many features.

Major competitor would be Embarcadero ERStudio. I imagine they are pretty similar.

If you want a similar question, check here.

Answer 2

For MySQL I use the MySQL Workbench http://www.mysql.de/products/workbench/. However it only works with MySQL. The MySQL Workbench is user friendly. As far as I know it is available under an open-source licence, hence no licensing costs.

Answer 3

Object Role Modeling (confusingly abbreviated ORM, although they have first claim to it). (Here's another article). It's the only conceptual modeling tool available for relational data (the others - Erwin and its ilk - being at best logical modeling tools, with nothing much better than Entity-Relationship diagrams and DDL synchrnoization, which are also provided by Object Role Modeling tools).

The primary software product as long been Visiomodeler and its predecessors, and was rudely (but slowly) abandoned by Microsoft when it acquired Visio, but new implementations are starting to emerge. There's one for Visual Studio and another for Eclipse, last I heard.

As for your criteria:
Price: Visiomodeler is still a free download from Microsoft, and the others (Norma, etc.) were also free last I checked.

Portable: All are pretty much vendor-agnostic, even Visiomodeler.

Easy-to-use: Hard to evaluate, and in varying states of completion, but no easier or harder than others. YMMV.

Everything else I've seen mentioned so far falls in the category of ERD diagrammers notable mainly for market share.

Answer 4

I'm happy using Visio Professional.

The main reason I use modelling tools is to create printable or uploadable diagrams.

Visio Professional does include a reverse engineering tool, so I can quickly generate a diagram from an existing database.

But it isn't a specialised data modeller, it's a generic design tool with database modelling functionality. You get a great level of control over the look-and-feel of the diagram - if I want 3 of the tables to be coloured Magenta, 2 more to use the Comic Sans Serif font, and a rectangle with rounded corners and a peach-coloured shadow bordering half the diagram, I can. (I'm using silly examples here, but the point is that it's useful to be able to annotate your diagram in any number of ways.)

Having said all this, it doesn't meet your explicit criteria, because it doesn't generate a data schema from a diagram.

Answer 5

I recently did the research on this and came up with ModelRight as a pretty good though relatively inexpensive tool for this sort of thing. For more details, you can check out my full answer at: http://stackoverflow.com/questions/272816/what-is-a-good-visio-enterprise-architect-replacement.