Show All

Hide All

Show me an example

1. To start your database model diagram, do one of the following:

Create a new model diagram from scratch

1. On the File menu, point to New, point to Database, and then click Database Model Diagram.
2. On the Database menu, point to Options, and then click Document. In the Database Document Options dialog box, select the symbol set you want to use and other table and relationship options, and then click OK.

Use a reverse engineered model diagram as a starting point

1. On the File menu, point to New, point to Database, and then click Database Model Diagram.
2. On the Database menu, click Reverse Engineer.
3. On the first screen of the Reverse Engineer Wizard, do the following:
• Select the Microsoft Office Visio database driver for your database management system (DBMS). If you have not already associated the Visio database driver with a particular 32-bit ODBC data source, do so now.
• Select the data source of the database you're updating. If you have not already created a data source for the existing database, do so now.

Notes

When you create a new source, its name is added to the Data Sources list.

• When you are satisfied with your settings, click Next.
4. Follow the instructions in any driver-specific dialog boxes.

For example, in the Connect Data Source dialog box, type a user name and password, and then click OK.

5. Select the check boxes for the type of information you want to extract, and then click Next.
6. Select the check boxes for the tables (and views, if any) that you want to extract, or click Select All to extract them all, and then click Next.
7. If you selected the Stored Procedures check box in step 5, select the procedures that you want to extract, or click Select All to extract them all, and then click Next.
8. Select whether you want the reverse engineered items added automatically to the current page.
9. Review your selections to verify that you are extracting the information you want, and then click Finish.

The wizard extracts the selected information and displays notes about the extraction process in the Output window.

10. Create a diagram of your model by
• Dragging the tables you want to view from the Tables and Views window onto the drawing page.
• Selecting a table, and then on the Database menu, clicking Show Related Tables to view the tables related to the selected table.

Import and refine an existing model

1. On the File menu, point to New, point to Database, and then click Database Model Diagram.
2. On the Database menu, point to Import, and then click Import <model type>.
3. Type the path and file name for the model you want to import, or click the Browse button to locate the model file, and then click Open.
4. Click OK in the Import dialog box.

Visio imports the file and displays its progress in the Output window. The imported tables are displayed in the Tables and Views window.

5. In the Tables and Views window, select the tables you want to model, and then drag them onto the drawing page.
2. Once you start a database model diagram, the real work of refining it begins. You can add

Tables

1. Open the database model diagram.
2. From the Entity Relationship or Object Relational stencil, drag an Entity shape onto the drawing page.

Columns

1. Open the database model diagram.
2. Double-click the table to which you want to add a column, and then in the Database Properties window, in the Categories list, click Columns.
3. Do one of the following:
• Click the Physical Name field of a blank line, and then type the name of the column. Press the ENTER or DOWN ARROW key and repeat for each additional column that you want to add to the table.
• Click Add. To add several columns to the table, click Add several times.

The default name for the new column follows the naming conventions you specified in the Database Modeling Preferences dialog box. To change the name of the column, click the default column name, and then type a new name for the column. Repeat to add additional columns to the table.

4. Click either Physical Data Types or Portable Data Types to display the data types your database model uses in the Data Type field.
5. To change the data type for a column, click the column's Data Type field, and then select a data type from the list, or simply begin typing the data type, including any parameters, and press ENTER. For example, you can type decimal(8,2) or char(30).
6. To prevent null values, select the Req'd check box.
7. To specify that the column is a primary key, select the PK check box.

The database model diagram is updated.

Note  You can press the TAB key to move from cell to cell, and you can use the SPACEBAR to select and clear the Req'd and PK check boxes.

There are more column properties in addition to those that appear when you click the Columns category. To edit additional column properties, click Edit.

Foreign key relationships

1. Open the database model diagram.
2. Click the Connector tool on the Standard toolbar. Position the Connector tool over the center of the parent table so that the table is outlined and drag to the center of the child table. When the outline appears on the child table, release the mouse button. Both connection points appear red, and any primary keys in the parent table are added as foreign keys to the child table.
3. Define the relationship by editing its properties.

How

1. Open the database model diagram.
2. Double-click the relationship whose properties you want to edit to open the Database Properties window.
3. In the Database Properties window, click the category that contains the information you want to edit, and then make your changes.

The database model diagram is updated.

Data types

1. Open the database model diagram.
2. Double-click the table that contains the column to which you want to add a composite data type, and then in the Database Properties window, in the Categories list, click Columns.
3. Click the name of the column to which you want to add a composite data type, and then click Edit.
4. In the Column Properties dialog box, click the Data Type tab, and then select the composite data type from the User Defined Type list.
5. When you are satisfied with your settings, click OK.

Tip

You can also add a composite data type to a table by clicking the Data Type box for the column to which you want to add a composite data type and either clicking the composite data type or typing the name of the composite data type.

3. To make other refinements to your diagram, you can

Create indexes

1. Open the database model diagram.
2. Double-click the table to which you want to add an index, and then in the Database Properties window, in the Categories list, click Indexes.
3. Click New.
4. In the Create Index dialog box, type a name for the index, and then click OK.
5. In the Index Type list, select an option to create a unique or non-unique index.
6. In the Available Columns list, select the name of each column you want to include in this index, and then click Add >.
7. In the Indexed Columns list, select the Asc check box to create an index that has an ascending sort order, or clear it to create one that has a descending sort order.

The database model diagram is updated.

Create views

From the Entity Relationship or Object Relational stencil, drag a View shape onto the drawing page.

Set extended properties for tables and views

Double-click the table or view whose extended properties you want to set, and then in the Database Properties window, in the Categories list, click Extended.

4. You can also add code for the following:

Check clauses

1. Double-click the table to which you want to add a check clause, and then in the Database Properties window, in the Categories list, click Check.
2. Click Add to open the Code Editor.
3. On the Properties tab, type a name for the check clause.
4. On the Body tab, enter SQL statements for the check clause. When you're finished, click OK.

The check clause is added to the Local Code list in the Code window.

Stored procedures

1. If the Code window is not already open, on the Database menu, point to View, and then click Code to open the Code window.

Note  This command is available only in the Database Model Diagram template.

2. Click Global Code, and then click New to open the Code Editor.
3. On the Properties tab, type a name for the code item. Under Type, click Stored Proc, Function, or Raw DDL.
4. On the Body tab, type SQL statements for your code.
5. When you're finished, click OK.

The code is added to the Global Code list in the Code window.

Triggers

1. Double-click the table to which you want to add a trigger, and then in the Database Properties window, in the Categories list, click Triggers.
2. Click Add to open the Code Editor.
3. On the Properties tab, type a name for the trigger.
4. On the Body tab, enter SQL statements for the trigger. When you are finished, click OK.

The trigger is added to the Local Code list in the Code window.

5. If the physical database was changed after you reverse engineered it, you can update your diagram to reflect those changes.