Fog Creek Software
Discussion Board

Database Design tools

I currently use Visio for all my database design needs...  I don't really know too much about other tools, such as ERWin or Datanamics Dezign.  Should I look into these other tools?  What do they do that Visio can't?


Saturday, August 16, 2003

Good question! I too use Visio and find it does everything I need. It supports entity relationship diagrams. I think the other tools can support a larger variety of RDBMs perhaps?

Visio gets no respect in the DB world it seems.

Saturday, August 16, 2003

Does Visio actually generate the DDL commands?

Saturday, August 16, 2003

According to the Visio web site, the Professional version generates DDL and supports reverse engineering. 

Saturday, August 16, 2003

The real question here is do you want to use some tools to draw out a ER diagram, or do you want to actually use the tools to manage, and CREATE your database schema?

Like the other posters here, I often use Visio for INITIAL  design work, but once actual work starts on the database, then I use the built in tools of that database. In fact, I only generally draw the tables (but NO fields) with Visio. So, I draw out the tables names, and refine the table layout somewhat.

Here is  screen shot of what I mean:

However, once I start defining field names, then I drop out of Visio, and into the product’s own built in tools

Of course, you can get Visio to generate ddl, but why? Further, you then run into problems as to which tool you will use to make changes?.

So, in the case of sql server, the Enterprise Manager is fine.

In the case ms-access, then again the built in relationship editor is fine. (in the ER diagram editor, you can even right click on any table and jump right into table design mode, not too bad for a little desktop database.).

However, the real big question here is should one use Visio or other tools to actually mange, and implement database schema changes?

My view on this one is no!

The reason here is that any developer who knows sql server can walk in and know/use the Enterprise Manager. If you start using a another tool, then you have to rely on that tool, and any new developer or user will then have to start also using that tool. I suppose if you are supporting multiple databases with one schema, then some modeling tool (with ddl) might make sense.

There is the balance of using another tool (with additional cost and support and training of that tool) vs using the built in tools. I mean, I would rather learn the built in tools, and thus know that any company I walk into will be using those tools.

I used to always install a ftp client on my personal computer. However, I learned how to use IE6, and it is a fine ftp client. It even supports drag and drop, and thus the window really now behaves like any other windows. (you have to enable folder view). The advantage of using and working with the familiar IE6 tool is that ALL PC’s will have this feature built in. Thus, any computer I will use can be assured of having a ready to go ftp client. 

You can be sure that a developer will know how to use the Enterprise manger if we are talking about sql server. So, I kind like sticking with a standard set of tools

Of course, with products like MySql, you don’t have a built in ER editor, so in that case, a modeling tool is for sure a good idea.

However, the main problem is that different databases have different features and thus features from one database to another are not equal. Thus, some database have things like Referential Integrity, and even that varies from vendor to vendor as to what RI features are implemented.

The other important issue is how large is the company? I mean, in smaller projects with just 2 or 3 developers, then often all 3 of the developers are allowed to make changes to the database schemata. However, in a very large company, there might be just 3 people modeling and documenting the existing databases, and NO developer can make changes to the database structures (they must all go to the database modeler). So, size of the system, and how formalized the process is can become a full time job. When the task gets to be this large, and requires a full time people to just mange the database structures, then modeling tools can be a help, as they tend to be better at documenting changes.

The other area where the commercial tools are valuable in migration to different platforms. If you need to move the data from one system to another, then some ER tool that functions on both databases systems is a real winner.

Another area is if you do consulting. Often, you have to go into a company and give advice on the current database designs being used. If you learn to use one good modeling tool, then likely you can use that tool to reverse engineer all the Major databases out there (Sybase, Oracle, sql-server, mysql, and ms-access). In this regards, you as a consultant only need to really learn ONE tool, and it can get you some nice documents for your recommendations. If you know your diagramming tool well, then with correct permissions, you can diagram and produce nice diagrams even on a database system you are not familiar with!.

Visio Professional has this ability to reverse engineer many common database systems.

Visio can also map your network or even map out a web site. You can also have the product map out your directory services!. It is also supposed to do some code mapping also. I have Visio 2000 installed, and the new xp Pro version has been sitting on my shelf for months. Hopefully I will get around to installing it  soon! Anyway, you do get a real grab bag of features with that product.

However, like a Swiss army knife, it means that the none of the features are the best!

I think Visio is great tool for developers, and making all kinds of documents, diagrams, and supporting project documents it is a great tool.

However, as database management tool, hum, I am not sold yet.

Albert D. Kallal
Edmonton, Alberta Canada

Albert D. Kallal
Saturday, August 16, 2003

Google for ORM Object Role Modeling

Simon Lucy
Saturday, August 16, 2003

I've used ER/Win, TOAD, Embarcadero's ER/Studio, EM, and Datanamic's Dezign. I've found Dezign to be the best for laying out initial databases. When I have to throw down a dozen or more tables, I like working with my hands on the keyboard as much as possible, and Dezign is the best at that. Everything else always requires clicking all over the place for each table and, in some cases, each field.

And yes, it does generate DDL *and* reverse engineer.

It also prints very well.

Finally, it's only $230, as opposed to four figures each for the "big guys"


Saturday, August 16, 2003

Visio Enterprise has a complete implementation of tools for ORM that Simon mentions in his earlier post.

Herbert Sitz
Saturday, August 16, 2003

If you have visio already, use it.

You might want to check out Case Studio. I like it.

Saturday, August 16, 2003

CASE Studio is the best low-cost E/R tool I've used.  I reccomend it highly.

Colin Evans
Saturday, August 16, 2003


Sunday, August 17, 2003

Try a 45-day free eval of sybase powerdesigner. Should give you a good feel for am enterprise class product, finish your project and motivate mngmnent to consolidate on one product that does Business process modelling (with simulation - not animation -SIMULATION), UML, XML (like XML-spy) and data modelling)

Sunday, August 17, 2003

And the price of Sybase Powerdesigner?

I haven't tried that one, but I *have* used "enterprise class" products, notably the industry standards of ERWin and ERStudio. IIRC, both are over $5k/user, and Dezign ($220) is easier to use than either of them.


Sunday, August 17, 2003

Stay away from over-priced tools like those from Rational. I'm using Embarcadero because the company has it but I'd wouldn't recommend it for the money.

You can find great tools for low money, like Datanamic's Dezign.

Tuesday, August 19, 2003

I recomend Database designer for MySQL

Not chep, but have all neccessary functionality for mysql database developement.

Wednesday, June 2, 2004

*  Recent Topics

*  Fog Creek Home