Use Visio to conduct data modeling and database reverse engineering

by Brian Kennemer | Nov 21, 2000 8:00:00 AM

Takeaway: If you're just using Microsoft Visio to draw charts and diagrams, you're only scratching the surface of its features. Learn how to use Visio to perform data modeling and reverse engineering by following these easy steps.

Many IT consultants use Visio for network design and architecture, Active Directory design, flow charts, process engineering, and to create sales presentations. A lot of them may not realize, however, that Visio 2000 can perform a staggering number of complex tasks. In this article, I’ll take a brief look at some of its other cool uses, including data modeling and reverse engineering tools.

Data modeling: This is the database design process that aims to identify and organize the required data logically and physically. A data model conveys what information is to be contained in a database, how the information will be used, and how the items in the database will be related to each other.
Reverse engineering: This is the process of analyzing an existing system to identify its components and their interrelationships in order to create representations of the system in another form or at a higher level of abstraction.

Courtesy of FOLDOC
Data modeling
Visio allows you to draw a database diagram using its familiar drawing tools. (The Database drawing type isn't installed by default. It must be selected when Visio is installed or added before you proceed with this process.) Start by selecting New from the File menu. This drops down a menu of various drawing types. From this list, click on Database, then on Database Model Diagram. This template provides you with an Entity Relationship “Stencil” that gives you the various shapes that would be used when drawing tables, relationships, and data views. Up to this point, the feel of the product is the same as when it’s used to draw process flows or other business charts. The cool part starts when it allows you to define the details for this table. Once you drag the Entity onto the drawing, double-click it, and the Properties dialog box will appear. This window holds all the information about the selected table.

Database Properties

In the figure above, the Database Properties window shows the Columns tab. Here, you can define the fields for the table, their data types, and other properties. From this same window, you can define the table’s Primary IDs, Indexes, Triggers, and Checks. A sample of the Trigger Code Editor window is seen below.

Trigger Code Editor

Once you’ve created your tables and defined their fields, you can use Visio to create the database. The Generate command on the Database menu will open a wizard that will walk you through the process of creating the database you’ve drawn on the screen. You choose whether to create the database in Access, SQL Server, Oracle, or a number of other databases. Here’s how it’s done:

Click on the Database menu and select Generate. This will bring up the Generate Wizard as seen in the graphic below.

Generate Wizard

Click the Generate New Database check box. This will bring up the next screen of the wizard, as seen below.

Database Connection

Select the type of database to create. For this example, I’ve selected Microsoft Access, and I’ll assume there is an existing blank mdb file. Click Next.
The next screen asks you to specify an ODBC (open database connectivity) data source. I’ve created a new one pointing to my existing database. The wizard has screens that will walk you through this process.
After selecting your data source, the next screen will allow you review the tables and other objects that will be created. The figure below shows this screen. If you’re satisfied with the tables, click Next.

Table Review

The wizard will then do a series of checks to ensure that the model you’ve created in Visio will function properly in the application in which you’re creating it. If it works properly, you’ll be prompted with a Finish button. Click it and your database will be created.

Now you have the database from your model created for you in Access. But what if you want to generate a Visio model from an existing database? Visio can do that for you, too.

Reverse engineering
Start with a new, blank database model diagram by clicking on File | New | Database | Database Model Diagram. Now you will see the Database menu on the menu bar. Click it, then select Reverse Engineer. From the first screen, as seen below, select the ODBC data source of the database you want to diagram.

Reverse Engineer

For this example I’ll create a diagram of the database I just created with the Generate Wizard above. Clicking Next brings up a screen that lets you specify which of the database elements you wish to diagram in Visio. The figure below shows these options. Make your selections and click Next.

Object Selection

The next screen displays a list of the tables in the database and lets you select which of those you wish to appear in the diagram. (In this example, there is only one table, so I will not show this screen.) Clicking Next from this screen brings you to the final screen, where you can review your selections. Clicking Finish will start the generation process. When the process is complete, you’ll see a popup window that displays each of the tables from the database. From this window, you can drag the tables into your drawing. Once they’re in the drawing, you’ll see the fields and keys for the table, as in the figure below.

Tables window and Table 1 in diagram

If you’re interested in other database modeling and creation tips, check out the Microsoft Visio Web site for details.
Do you use Visio for data modeling or database reverse engineering? Does it work well for your needs? Post a comment below or send us a note.

11 comment(s)