Visio makes it easy to create database models from imported schemas

by Lamont Adams | Dec 09, 2002 8:00:00 AM

Takeaway: Database developers will find the Enterprise Architect version of Vision 2002 to be very handy. Learn how to use Visio's new data modeling tools to document and make changes to your databases.

Microsoft Visio has long been one of my favorite tools, even before it began sporting the Microsoft trademark badge. This is probably because I’m a bit of a diagram freak: Even the tiniest project of mine involves the creation of some kind of diagram, especially a database project.

The Enterprise Architect edition of Visio 2002 includes a set of wizards that database developers will find incredibly useful. You can create a database model or diagram by simply importing the schema from an ODBC or OLE-DB database. And you can actually use Visio as a graphical database front-end of sorts, exporting any changes you make in the model back to your database.

Building a model from an existing database
We’ve all been in situations where you've been given an undocumented database created by someone else and asked to make major changes to it. In that situation, you can spend days looking over views and stored procedures in an attempt to familiarize yourself with the database, and then still get blindsided by an unanticipated side effect after making your changes. But using Visio to import a model can make this concern moot.

To create a diagram from an existing database, you first select the sort of diagram you want by opening one of the database drawing templates. Once Visio loads the template, choose Reverse Engineer from the Database menu. You’ll be presented with the dialog box shown in Figure A, which prompts you to choose a DSN for the database from the list or create a new one. When you’re done, click Next.

Figure A

Choose a DSN for your database.

After specifying a user name and password for the DSN connection, you’ll be prompted to choose which types of database objects you want Visio to include in the model. You can pick from particular table elements or exclude tables altogether and include only views or stored procedures, as shown in Figure B.

Figure B

Choose the types of database elements to include.

After you specify the types of objects, you’ll be prompted in the next few dialog boxes to select the individual elements you’re interested in. After doing so, you’ll be presented with a final confirmation dialog box (Figure C). Visio then does its thing.

Figure C

Click Finish to generate your model.

I was impressed by how little time the process actually took to have Visio construct both ER diagrams and database models for the pubs SQL Server 2000 sample database. I was expecting to wait a few minutes, but the process only took about 10 seconds, even when I had Visio set to inspect everything. Your mileage may vary because pubs isn’t the most complex database on the planet, and my test server wasn’t exactly in heavy use. But I still think you’ll be pleased by the relatively short amount of time it takes to have Visio build a diagram, especially compared to the amount of time it would take to build one by hand.

Modifying a schema
Okay, so you have your database model. You should just print it out and be done with it, right? Well, you might not want to be so hasty. As long as you have a live connection to the database, Visio can actually propagate the changes you make to the model back to the database.

When you select a table in your model, Visio presents you with a Database Properties tool window, shown in Figure D. Using this window, you can inspect properties for the table itself, and the columns, indexes, triggers, and constraints the table contains. You can also modify these table elements and add new ones. Use the Portable Data Type option at the bottom of the window if you don’t plan to export your changes to a SQL Server database. You then use the Database>Generate menu command to generate the DDL statements needed to update your database with the changes you made.

Figure D

This shows the process of adding a new field to the Employees table.

What’s the big deal?
Visio’s support for round-trip engineering is a boon for developers stuck with undocumented databases they didn’t design. Although I don’t see anyone creating a full-scale system using Visio as a front end—you certainly could; the question is why would you—I can see people doing prototyping or initial schema generation by exporting a Visio database diagram.

5 comment(s)