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

Closed. This question is opinion-based. It is not currently accepting answers.

---

Want to improve this question? Update the question so it can be answered with facts and citations by editing this post.

Closed 2 months ago.

Improve this question 开发者_Go百科

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)

---

A great question. First things first. As you have implied, a data model tool is not required to build and maintain a good data model. But, if you make it part of your engineering cycle, it can speed implementation time and drastically cut down on maintenance costs over the life of your product.

To justify the use of a tool, it has to be an integral part of your development. To be integral, it must be easy and intuitive to use and should not impair your overall productivty.

If you just use it to create pretty pictures, the diagrams will quickly become obsolete and the effort required to maintain it will constantly be pitted against real engineering tasks.

To put it another way, if all Eclipse or Visual Studio did was beautify our code for printing purposes, we'd still be using notepad.

The right ER tool must materially improve your ability to deliver and facilitate your ability to communicate design to technical and non-technical staff. The right tool will make you look awesome (more awesome?).

In addition to creating an initial model that you can show to visually explain your design, a great ER tool should do the following:

• Generate usable schema scripts directly from your model.
• Merge changes made to a live copy back into your model.
• Create change scripts based on a comparison between your model and a live database.
• Allow you to maintain data definitions and publish them with little effort.
• Translate your design into any of the major flavors of SQL.
• Allow you to create visually appealing sub-models that automatically sync to the main model.
• Allow you to define entity templates, so you don't have to keep creating the same system columns (insert date, insert user, modify date...) for all tables.
• Provide you with both logical and physical views of your model, so you can use both human-readable column names (First Name) as well as physical column names (first_nm) in your diagrams. The former is best for non-engineers while the latter provides you with the ability to name columns based on your own best practices.
• Provide you with a painless way of versioning your model over time.

ERWin, MySQL Workbench and ER/Studio are examples of tools that are intuitive to use, allowing you to focus on your design and not on how to use the tool to do basic tasks. There are others. These are the ones I have experience with.

ERWin currently has a freeware version that you might find useful in determining whether a tool will work for your environment.

Visio only provides you with the ability to reverse-engineer for the initial model.

I hope this helps you in your decision.

---

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.

---

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

---

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.

---

From what I have found out, there are a number of advantages a dedicated DB diagraming tool has over a basic diagram-oriented tool like Visio:

1. Support to generate a diagram from an existing database
2. Support to generate an alter script to apply the changes you made to a diagram
3. Support to compare a diagram against an existing database

There are many others, but in a nutshell, the dedicated tools will have many more integration features that leverage the schema of existing databases, which make managing them MUCH easier, especially in a transition scenario.

---

To be honest, I think that generating the DDL is pretty much a hygiene function these days. If your project is a "heavy lifting" database project, and you have a DBA and all those good things, you will almost certainly develop a project approach to the database; that approach may well include logical and physical design, validation, sample data, traceability to requirements and all that good stuff. In that case, get a proper tool - though be aware that many of them have a fairly steep learning curve, and want you to see the world in their idiosyncratic way. I never did manage to get the hang of ErWin, for instance, but found StarDesigner to be much more to my tastes...

If your database is just where your business layer stores its data, and the database is not the key driver for the project (which is usually the case with web apps), I wouldn't stress about it too much - you'll get a far bigger bang for buck with looking at continuous database integration and other ways of allowing a database to evolve under development.