What is the point in using a "real" database modeling tool?

Question

We currently have a 10 year old nasty, spaghetti-code-style SQL Server database that we are soon looking to pretty much re-write from scratch as part of a re-write to a large web application. (The existing application will serve as the functional requirements for the next incarnation of the app).

Some have suggested we use Visio to do all the diagramming and to generate the DDL, but others have suggested we use a dedicated database design tool, rather than a diagramming tool that is able to export DDL.

Is there any benefit to using "real" DB design tools, such as ModelRight, over general tools like Visio? If so, what are those specific benefits?

---

Edit: In a nutshell, what can real/dedicated tools do that something like Visio can't, and how much do these capabilities matter and/or are they worth the cost? (from a best-practices standpoint, for example)

Answer 1

there is an Oracle tool - Enterprise Elements
that tool allows you to create the database dynmaically by entering the metadata about the system, and all UI is then generated for you. you can change the model on the fly, which automatically adjusts the reporting and web based forms etc. maybe a good choice for a redesign that you want to remain fluid

Answer 2

I would choose the one that involves lot of inexpensive attempts to create a schema and lots of real mockup code of sample transactions. If it doesn't support round tripping (ie. rewriting the diagram from the schema and writing the schema to match changes in the diagram) you will be tempted to not keep tweaking the schema because it would take forever to get them to match each other again.

So the database diagrammer in Enterprise Manager (and I think Management Studio) would be far superior to Visio.

Answer 3

I'm using MySQL Workbench.

http://en.wikipedia.org/wiki/MySQL_Workbench

http://wb.mysql.com/

Answer 4

As of Visual Studio 2008, there is a database edition which has a SQL Server 2005 project type. With this you can take a model from a database (model in SSMS diagram) and generate script for it. Next you can do schema compares to different versions of your model and use the resulting scripts to update older versions to newer versions. The same can be done with data.