Whether you've inherited the company's e-commerce back end and want to document it, or you simply need to understand the legacy purchasing system, an accurate database model diagram can help. Although Visio can reverse engineer a database and create a diagram based on the schema, the result is more than a series of connected shapes. Visio creates a model that represents the logical and physical structure of a database, including tables or entities, columns or attributes, and relationships. Visio supports notation for primary keys, foreign keys, alternate keys, and indexes as well as referential integrity and IDEF1X cardinality constraints. To view and edit this information, you work in several special-purpose anchored windows as well as the drawing page, as Figure 19-1 shows.
The Database Model Diagram template is an application unto itself. As with other drawing types, you can drag shapes to add objects to your database model. However, the template adds the Database menu and other tools specifically for working with your database model diagram. Because of these additions, the way you create and modify database diagrams doesn't necessarily resemble the way you work with other Visio drawing types.
Figure 19-1. You can reverse engineer a database to extract tables, views, code, and other database elements and create a database model diagram.
Most people use the Database Model Diagram template to reverse engineer a database that already exists. However, you can also use the template to prototype new designs
Visio 2000 Enterprise Edition included advanced database modeling tools as well as integrated support for object role modeling, all of which are now included with Microsoft Visual Studio.NET Enterprise Edition. Visio Professional delivers a subset of these tools. With the Application Design Workbench in Visual Studio.NET, you can reverse engineer a database schema to create a diagram as you can in Visio Professional; however, you can also generate a database from the diagram and keep the model and database in sync and perform model validation and error checking. See the MSDN Web site (http://msdn.microsoft.com/vstudio/) for more information.
by dragging and dropping shapes that represent tables or entities and connectors that show relationships. The Database Model Diagram template opens with the Entity Relationship stencil for creating relational database models and the Object Relational stencil for creating object-relational database models with extended functionality such as composite data types, type inheritance, and table inheritance.
Follow these steps to start a new database model diagram and set modeling options:
Tip - Import a Database Model Diagram
If you have an existing database diagram from PLATINUM ERwin or VisioModeler that you would like to edit in Visio, you can import it. Visio Professional can import ERX files from ERwin 2.6, 3, and 3.52 (although Visio doesn't import stored procedures, custom triggers, or ERwin displays). You can import models from VisioModeler 2 or later. To import a model, choose Database, Import.
The Database menu is no longer visible.
Solution-specific menus can disappear when Visio encounters an unexpected error from an external source. If the Database menu no longer appears between the Shape and Window menus, choose Tools, Options. On the Advanced tab, select Enable Automation Events, and then click OK. Save changes to any open diagrams, and then exit Visio. Restart Visio, and then open your database model or start a new diagram with the Database Model Diagram template.
You can add shapes to the drawing page to provide one view of a database model. Visio stores another view of your model in the Tables And Views window, which lists the names of tables and views extracted from a database, as Figure 19-2 shows. To display this window, choose Database, View, Tables And Views. Whether or not the diagram shows every table and view in your model, the Tables And Views window does. You can drag elements from the window onto your diagram, where you can view them along with any associated relationships.
Figure 19-2. The Tables And Views window is an anchored window that you can float or dock in a convenient location as you work on your database model diagram.
When you start a new diagram with the Database Model Diagram template, the Output window appears below the drawing page. This window displays progress and status information when you reverse engineer a database, as Figure 19-3 shows. You can verify that the database schema was extracted as specified and view a summary at the bottom of the window of the number of tables and other items, including code, that were extracted from your database.
Figure 19-3. The Output window shows you every step Visio takes when reverse engineering a database.
Visio validates your model when you reverse engineer a database. Messages about conflicting names, data types, or other errors appear in the Output window. You can copy the messages and paste them into Notepad or another text editor to keep them on hand for troubleshooting. Right-click in the Output window, and then choose Copy Message or Copy All Messages.
If your database includes code, such as check clauses, or stored procedures, Visio can extract it when you reverse engineer a database. The Code window lists all code for a particular database platform, as Figure 19-4 shows. You can view, edit, and delete stored procedures, functions, triggers, check clauses, view definitions, and even raw DDL (data definition language) code, each of which is represented by a different icon in the Code window.
Figure 19-4. You can view, edit, and delete the code associated with your database model diagram.
To display the Code window, choose Database, View, Code. The Code window lists two types of code:
To display any code module, select it, and then click Edit to open the Code Editor. The Code Editor is a full-featured editing environment. You can specify keyword colors, assign keyboard shortcuts, and more with the Window Properties button on the Code Editor toolbar. For example, you can indent lines automatically as you enter code in accordance with language-specific scoping rules. Use the Insert Code Skeleton button to do just that—insert a blank code skeleton for the type of code you have specified.
You can set preferences for the way shapes look and behave in a database model diagram. For example, you can specify whether deleting a shape from the diagram deletes the object from the model. When you choose Database, Options, Modeling, you specify the default settings that are stored with the Database Model Diagram template and remain in effect until you specify otherwise. As Figure 19-5 shows, the Logical Diagram tab includes the following options:
Figure 19-5. With the options on the Logical Diagram tab, you can customize the behavior of the shapes that represent your database model.
On the Logical Misc tab, you can control the way names and relationships appear in the diagram so that shapes reflect your preferred modeling style, as Figure 19-6 shows. You can set the following:
Figure 19-6. On the Logical Misc tab, you can choose how foreign keys are propagated and how to build default names and resolve name conflicts.