Modifying a Database Model

Visio Professional provides extensive options for working with the tables, columns, views, and relationships in a database model diagram. You can define and edit settings for data types, referential integrity, indexes, and extended attributes, and see your changes reflected in the shapes on the page.

Visio displays detailed information about the database shapes in the Database Properties window, as Figure 19-13 shows. To display this window, right-click a shape, and then choose Database Properties.

Figure 19-13: For convenience while editing the diagram, drag the Database Properties window by its title bar into the Output window to merge the windows at the bottom of the screen.

The Database Properties window makes it easy to refine the tables in your database model. In the Categories list, you can select an item to work with its properties. Table 19-1 summarizes the options available for each category.

Table 19-1: Categories in the Database Properties Window

Category	Description
Definition	Use this category to specify physical and conceptual names for the table and choose whether to synchronize the names. You can specify a namespace value for the table to distinguish it from similarly named tables in the model. Definition also displays information reported by the host DBMS about the database owner and the path of the source database. The defining type field is available only when a table is empty; it lists all composite data types, so you can create a typed table.
Columns	Use this category to add, remove, edit, and change the order of columns in a table. You can identify a column as a primary key or foreign key and specify whether physical or portable data types are displayed.
Primary ID	Use this category to edit, define, or delete primary keys from a list of available columns and to choose whether to create an index on primary keys.
Indexes	Use this category to create, edit, define, rename, delete, or set extended attributes for indexes. Use this tab to specify the type of index you create for a particular column.
Triggers	Use this category to add, edit, or remove the code for triggers that are included with your model. When you click Add or Edit, the Code Editor opens so that you can create or edit a trigger.
Check	Use this category to add, edit, or remove the code for check clauses that are included with your model. When you click Add or Edit, the Code Editor opens so that you can create or edit a check clause.
Extended	Use this category to set DBMS-specific extended attributes for use with the Visio database drivers.
Notes	Use this category to add notes about a table.

Adding and Editing Tables and Columns

Visio represents a table in your database model diagram with the Entity shape. When you want to add a new table to the diagram, you can drag a shape from either the Entity Relationship or Object Relational stencil depending on the modeling methodology you want to use. Visio makes changes to the appearance of the Entity shape based on the modeling and display options you have specified. For example, if you specify IDEF1X, Visio displays the relationship between parent and child tables, as Figure 19-14 shows.

Figure 19-14: If you’re using IDEF1X notation, Visio displays dependent tables with rounded corners.

Note

There is no graphical notation for independent and dependent tables if your model uses Relational notation. In that case, primary, alternate, and foreign keys are visible on the table.

When you specify Relational notation for your database model diagram, the Entity shape has a shaded box at the top that contains the table’s conceptual name, as Figure 19-15 shows.

Figure 19-15: If you’re using Relational notation, you can display primary keys, foreign keys, and indexes on the table.

Tip

To change the modeling notation, choose Database, Options, Document. Under Symbol Set, select the notation you want to use, and then click OK.

Follow these steps to add and name a new table:

1. Drag an Entity shape from the Entity Relationship or Object Relational stencil onto the drawing page.

2. If the Database Properties window is not visible, right-click the new table, and then choose Database Properties.

   

3. Type a name in the Physical Name box. Visio updates the Conceptual Name box if the Sync Names When Typing check box is selected (the default); otherwise, you must type a value for Conceptual Name.

   Tip

   To display the Database Properties window, double-click a table shape.

Adding Columns to a Table

You can add columns to an existing table or define columns for a new shape in the Database Properties window. When you add a column to a table, you can define it as the primary key. If you’re using Relational notation, primary, alternate, and foreign keys are visible on the table, as Figure 19-16 shows. Visio automatically creates unique indexes on primary keys. Other columns can be assigned a nonunique index called the inversion key.

Figure 19-16: OfficeLoc is the primary key (PK); StreetAddress, IsHeadquarters, and Countryname are required values and appear in bold. PostCode, StateCode, and CityName are unique indexed columns (U1).

Before you start adding columns, you might want to define a default naming convention. Visio generates default names for columns, primary keys, foreign keys, and other attributes based on a prefix and suffix specified in the Database Modeling Preferences dialog box. For example, if you add a new column to the Orders table, Visio provides the name OrdersCol1. To specify default names, choose Database, Options, Modeling, and then change the settings on the Logical Misc tab.

Follow these steps to add a column and define its properties:

1. Right-click the table to which you want to add a column, and then choose Database Properties.

2. In the Categories box, choose Columns, and then click Add.

   

3. To create a primary key, place a check mark in the PK column.

4. To define the column’s attributes, click Edit.

   

5. In the Column Properties dialog box, click the tab that contains the information you want to define and make your changes.

Table 19-2 describes the options available in the Column Properties dialog box. Visio updates the database model diagram to reflect your choices.

Table 19-2: Options in the Column Properties Dialog Box

Tab	Options
Definition	On this tab you can type in the Physical Name box to specify the column’s name as it appears in the database or in the Conceptual Name box to specify the name as it appears in the database model. If you want to specify a default column value, type in the Default Value box, and then choose the Is Literal Value or Is An Expression Or Function Call option. Select the Allow NULL Values check box to make the column optional rather than mandatory.
Data Type	On this tab you can choose whether to display Portable or Physical Data Types and assign Data Types to columns.
Collection	On this tab you can specify whether the column in an object-relational model is a single value or contains a collection of information. Collections can be sets, lists, or multiple values.
Check	On this tab you can edit, add, or remove check clauses for a column. When you click Add or Edit, the Code Editor opens so that you can create or edit a check clause.
Extended	On this tab you can set DBMS-specific extended attributes for use with the Visio database drivers.
Notes	On this tab you can add notes about a column.

Tip

Choose Primary ID in the Database Properties window to define a primary key and customize its physical name and key type.

Categorizing Subtypes of Tables

When you have a large number of tables of the same type, or attributes that are repeated for several entities, you can define a category, which Visio represents with the Category shape, as Figure 19-17 shows. The Parent To Category and Category To Child connectors create one- to-one relationships between parent and child tables in the category. The parent table includes all the common attributes, or columns, for the category, including the discriminator, the value of which identifies the categories of the subtypes. Attributes unique to a category are assigned to the appropriate child, or subtype. Each subtype inherits the primary key of the parent automatically. Visio can represent complete categories, in which all subtypes are included, or incomplete categories, which include only some subtypes.

Figure 19-17: This complete category uses the C_Code column as the category discriminator.

Follow these steps to define a category:

1. Drag a Category shape to your database model.

2. Choose the Connector tool from the Standard toolbar, and then click the Parent To Category shape on either the Entity Relationship or Object Relational stencil.

3. Drag from the parent table to the Category shape.

4. With the Connector tool still selected, click the Category To Child shape, and then drag from the Category shape to the child table.

5. Repeat step 4 for each child table.

6. Right-click the Category connector shape, and then choose Database Properties.

7. If the category represents a complete category, select the Category Is Complete check box. An incomplete shape displays a single line below the circle. A complete shape displays a double line below the circle.

8. To specify a category discriminator, choose This Attribute, and then select the column you want to use.

Specifying Data Types

Each column in a table must be assigned a data type, and Visio provides advanced options for defining and assigning data types. You can use portable data types so that your model remains independent of the implementation requirements for any particular DBMS. However, if you’re documenting a specific database, you can use the physical data types specified by the target DBMS. When you reverse engineer a database, Visio displays physical data types by default. To see all the built-in physical data types available in your model, choose Database, View, Types to display the Types window.

The Types window also lists user-defined data types. If you’re working with portable data types, you can define your own types to provide consistent and reusable definitions for columns in your model.

Note

To display data types on shapes in a database model diagram, choose Database, Options, Document. On the Table tab, choose Show Portable or Show Physical under Data Types.

Setting a Column’s Data Type

When you assign a physical data type to a column, you must be familiar with the requirements of your DBMS. When you work with portable data types, you can use the default specifications or create a user-defined data type.

Follow these steps to specify the data type for a column:

1. Right-click the table containing the column you want to edit, and then choose Database Properties.

2. In the Categories box, select Columns.

3. For Show, select either Portable Data Type or Physical Data Type. Visio updates the Data Type column according to your selection.

4. Select a Column, and then click Edit.

5. In the Column Properties dialog box, click the Data Type tab. Visio displays either the mapped physical data type or the portable data type. To switch between two views, select either the Show Portable Data Type or Show Physical Data Type option at the bottom of the Column Properties dialog box.

6. To map the column to a different data type, click Edit, specify the options you want, and then click OK.

7. When you’ve specified the data type options you want, click OK.

Tip

New Feature You can also change the data type by selecting Columns in the Database Properties window, left-clicking in the Data Type column, and selecting a data type from the drop-down list.

Inside Out
Integration with Visual Studio .NET

Although Visio Professional is used primarily to document existing databases through reverse engineering, it includes advanced features for creating platform-independent models and working with database code. The designers of the Database Model Diagram template intend the diagrams to be compatible with the model generation features in Visual Studio .NET Enterprise Edition. For example, if you plan to implement a model on more than one type of DBMS, you can define portable data types. In Visio Professional, you can’t do much else with the portable data types. However, by optimizing your database model diagram in Visio Professional, you can then import it in Visual Studio .NET and work with the database generation features.

Creating User-Defined Data Types

When you define your own data types, you control the way Visio represents portable data types in your model. You create your own data types by using the User Defined Types command to define the type attributes, such as category (numeric, text, logical, and so on), type, and size.

Follow these steps to create a user-defined data type:

1. Choose Database, User Defined Types.

2. Click Add, and then type a name in the Data Type Name box.

3. To base your new data type on an existing one, select the Copy From check box, and then select the name of an existing user-defined data type.

4. Click OK to return to the User Defined Types dialog box.

5. Specify the category, type, and size in the appropriate box, and, if desired, type notes about the data type in the Description box.

6. Click OK.

Troubleshooting
Visio does not create the foreign key when you connect parent and child tables

Unless you change the default behavior, Visio creates a foreign key relationship when you connect a relationship line between two tables. In the child table, Visio adds a new column in and identifies a foreign key relationship between the two tables. When a foreign key is not automatically propagated, ask yourself the following questions:

• Has the default foreign key behavior been reset? On the Logical Misc tab of the Database Modeling Preferences dialog box, the Propagate On Add check box must be selected for Visio to create a foreign key relationship. Choose Database, Options, Modeling, and then verify that this check box is selected.

• Is the relationship shape glued to both tables? When you select a relationship connector, both endpoints appear red if the shape is properly glued to the two tables. If an endpoint is green, drag it slightly away from the table, and then press the Ctrl key as you drag it back to the table. A red border appears around the table shape to indicate that the shapes are connected (with shape-to-shape glue).

Specifying Relationships and Cardinality

Visio represents the parent–child relationships between tables in your database model diagram with the Relationship connector. You can control the way tables interact with one another—specifically, the way a child table inherits from a parent—by adding relationships and editing their properties. Relationship properties include direction, referential integrity, and cardinality, all of which can be displayed in your database model diagram depending on the notation you’re using. For example, in Relational notation, an arrow signals the direction of the relationship and points to the parent table. In IDEF1X notation, a dot specifies the child table, as Figure 19-18 shows.

Figure 19-18: Relationship connectors indicate parent–child relationships between tables.

You can use the Relationship shape from either the Entity Relationship or Object Relational stencil to add a relationship that defines a foreign key relationship between tables. However, the Connector tool on the Standard toolbar does the same thing, and it’s a little easier to use.

To add a relationship and specify its properties, follow these steps:

1. Click the Connector tool on the Standard toolbar, and then drag from the parent table to the child table. Visio adds any primary key in the parent table to the child table as a foreign key.

2. Right-click the new relationship connector, and then choose Database Properties.

3. In the Categories box, select the category that contains the information you want to edit, and then make your changes. See Table 19-3 for details.

   Table 19-3: Categories for Specifying Relationship Properties

   Category	Options
   Definition	Use this category to create associations between columns in the parent and child tables and to enter role names for foreign keys.
   Name	Use this category to type an optional phrase in the Verb Phrase box to describe the parent’s role and in the Inverse Phrase box for the child’s role. In the Physical Name box, you can type a name based on the requirements of the target DBMS. In the Notes box, you can add notes describing the relationship.
   Miscellaneous	Use this category to choose the type of cardinality, whether the relationship is identifying or nonidentifying, and whether the relationship is optional.
   Referential Action	Use this category to choose options that determine the effect on the child table when information in the parent table is updated or deleted.

Displaying Role Text on Relationships

To make your database model diagram easier to read, you can add role text to relationships. A role is the part played by an object in a relationship. To describe the role played by a parent table, you define a verb phrase. An inverse phrase describes the role in reverse, as played by a child table. Typical role phrases include is a, is of, or has a, as in Person is of Country, where the Person table and Country table have a foreign key relationship. Role text is displayed on the relationship connector, as Figure 19-19 shows.

Figure 19-19: You can display the verb phrase, inverse phrase, or both on a relationship. The inverse phrase “is of” appears here.

Follow these steps to define and display role text:

1. Right-click the new relationship connector, and then choose Database Properties.

2. In the Categories box, select Name.

3. Type the phrases you want to display in the Verb Phrase and Inverse Phrase boxes.

4. Choose Database, Options, Document.

5. On the Relationship tab, select Show Verb Phrase. If you want to display only the verb phrase, select the Forward Text check box. If you want to display only the inverse phrase, select the Inverse Text check box.

6. Click OK.

   Tip

   If the connector line obscures the role text, click the Text Block tool on the Standard toolbar, and then use it to drag the text out of the way.

Role Text and Object Role Modeling

The Verb and Inverse Verb Phrase options for relationships in Visio Professional are designed to work with conceptual models created with the Object Role Modeling (ORM) method. In ORM, you can design and query database applications using English-like language. The resulting models are simpler for nontechnical audiences to understand. Visio Professional includes a stand-alone ORM template, but you can’t use it to exchange data with the Database Model Diagram template.

You can, however, use Visual Studio .NET Enterprise Edition to define business rules in an ORM diagram and then upload the model into a relational or object-relational database model diagram, which in turn can be used to generate a script in DDL or a physical database schema. The ORM functionality in Visual Studio is based on a tool known as VisioModeler, now freely available as an unsupported product from Microsoft Corporation. To download VisioModeler (a 25-MB download), go to http://www.microsoft.com/downloads/search.asp?. For more information about ORM, visit http://www.orm.net, a site maintained by Dr. Terry Halpin, who formalized the ORM notation.

Displaying Referential Integrity and Cardinality

When you define a parent–child relationship between tables, you can specify referential integrity as well, which determines how changes to the parent table affect the child. You can display an annotation on the relationship connector to indicate referential integrity. In addition, you can indicate the cardinality of a relationship in IDEF1X notation. For example, in Figure 19-19, Crow’s Feet notation is used for the relationships and shows that the cardinality of the parent-to-child relationship is one to zero or more for both relationships.

Note

To display Crow’s Feet notation, choose Database, Options, Document, click the Relationship tab, and then select the Crow’s Feet check box.

To display referential integrity or cardinality, choose Database, Options, Document to display the Database Document Options dialog box. On the Relationship tab, select the check boxes for Referential Integrity and/or Cardinality, and then click OK.

Note

The Cardinality option is available only when you use the IDEF1X notation, which you can specify on the General tab of the Database Document Options dialog box.

Defining an Index

You can view and edit the indexes on columns that you reverse engineered and define new indexes for columns you plan to search frequently in your DBMS. The Database Properties window displays the attributes of an index, as Figure 19-20 shows.

Figure 19-20: You can see the properties of an indexed column when you select Indexes in the Database Properties window.

To add an index, click New, type a name, and then click OK. In the Available Columns box, select the names of the columns that you want to include in this index, and then click Add. The Disp. Name area displays the index notation as it will appear in your database model diagram. Table 19-4 summarizes the properties you can specify for an index.

Table 19-4: Index Properties

Option	Description
Index Type	Choose whether the index is unique or nonunique or represents a constraint in this list box.
Asc	Select this option to set the sort order to ascending.
Options	Use this button to set driver-specific extended attributes for the index. Not all databases support extended attributes.

Note

You can set extended attributes for a table, column, or relationship. Extended attributes are specific to a target DBMS.