Visio 2000 Enterprise Edition

A model database tool

Why rely on archaic tools to model your client database? Visio is under new management (Microsoft purchased the company in 1999), but Visio 2000 Enterprise Edition still delivers the powerful network-diagramming features that made it the darling of network architects. The product also provides software-modeling functions that simplify coding processes for programmers, and relational-database management components make the utility a must for anyone who works with Microsoft SQL Server 7.0, Microsoft Access, or other databases.

Least-Known but Not Last
At its heart, Visio 2000 Enterprise Edition is a diagramming tool that lets users accomplish a multitude of tasks. But the patriarch of the Visio 2000 family does a lot more than simple diagramming. The product offers AutoDiscovery, an SNMP-based utility that the software uses to discover network devices and create a network overview. And the product's software-modeling features let programmers create Unified Modeling Language (UML) diagrams of applications before they begin the coding process. The software can also reverse-engineer Visual Basic (VB) and Visual C++ (VC++) code into UML 1.2-compliant software models.

However, the product's least-known value lies in its database-management features. The software's foundation as a diagramming tool lets it create visual overviews of your relational databases. By constructing this visual representation, Visio 2000 Enterprise Edition can help you keep tabs on your SQL database. The software supports nearly every database in use today, including Access, Microsoft Visual FoxPro, Corel's Paradox, IBM's DB2, Oracle, and Sybase SQL.

Visio 2000 Enterprise Edition offers two methods for creating a database model. If you have a database, you can use the software to reverse-engineer your existing database into a visual structure. If you're creating a new database, you can use the software to build the model from scratch. I tested both methods on a Pentium II 350 processor system that had 128MB of RAM and ran SQL Server 7.0 on top of Windows 2000 Server.

The software ships on two CD-ROMs that contain the program, a comprehensive online manual that supplements the well-written and massive hard-copy documentation, and a large library of more than 22,000 shapes and sample diagrams that you can modify to suit your needs. Installing the software was easy: Using Windows Installer, I simply selected the appropriate options, then clicked Finish.

Reverse-engineering an existing SQL Server database down to its base model was simple. After I selected the File menu option to create a New Database Model Diagram, I selected the Reverse Engineer option from the Database menu. This action launched the Reverse Engineer Wizard, a utility that simplifies the arduous task of breaking a database down to its code level. The wizard connected to the database management system (DBMS) that I specified and extracted the database schema that I requested. After I selected the appropriate data sources and tables to model, Visio 2000 Enterprise Edition spun off a thread and converted the esoteric code into a logical model that displayed the database's tables and their relationships.

Creating a database model from scratch was inherently more complicated because I needed to fill in all the values, but the product still went a long way toward simplifying the process. Again, I opted to create a New Database Model Diagram, but this time I used Digital Scratch Pad, which Figure 1 shows, and appropriate database stencils that the program provided. After I configured the program to use my SQL Server driver as the default database driver, I began to create a table by dragging an Entity shape from the stencil list onto the scratch pad.

Next, I right-clicked the Entity to open its Properties dialog box, on which I could define the table's physical and conceptual names. I named the table Inventory (a unique name that I used as a visual identifier). To create additional attributes for the database model, I used the options on the Columns tab of the Properties dialog box to define additional table columns (e.g., inventory types, the number of available items). I then dragged another Entity shape onto the scratch pad and repeated the process to create another table, which I named Customers. Using the Relationship shape, I connected the two tables. To share the created database model with other users, I saved it as an HTML file and published it to my Microsoft IIS server.

To keep the database and the database model synchronized, the program includes an Update Database Wizard, which uses a snapshot of the database schema and compares it with the database and the database model. When the wizard detects discrepancies, it offers to update either the database or the model. If your database is large, this update can take some time, but it's easier than creating a new diagram whenever you update the database. And because Visio 2000 Enterprise Edition is multithreaded, you can work with other diagrams while the program updates your models.

Using this product, I was able to create a complete and accurate database schema that I could then use as a design guide during database development. By creating a clearly defined database model that showed the relationship between my tables, I avoided headaches, such as sync errors, midway through the development process. The only problem with Visio 2000 Enterprise Edition is that it's purely a Windows program. If you work with other systems, such as Linux or FreeBSD, you can't use this product.

Visio 2000 Enterprise Edition offers IT professionals an all-in-one solution: Whatever you need to diagram, Visio can handle it. The product's price is another plus—with a $999 list price, this product costs $1500 less than Computer Associates' (CA's) ERWin but is just as capable. My recommendation to every IT professional: Get a copy of Visio 2000 Enterprise Edition.