Create a Database Model (also known as Entity Relationship diagram)

With the Database Model Diagram template, you can create a new model or reverse engineer an existing database into a model by using either the relational or object relational modeling concepts. Use the Entity Relationship stencil to model databases that are based on the SQL92 and earlier standards. Use the Object Relational stencil, which has additional shapes for working with types, to model databases that are based on SQL99 and later standards.

2010 2007



With the Database Model Diagram template, you can create a new model or reverse engineer an existing database into a model by using either the relational or object relational modeling concepts. Use the Entity Relationship stencil to model databases that are based on the SQL92 and earlier standards. Use the Object Relational stencil, which has additional shapes for working with types, to model databases that are based on SQL99 and later standards.

This article describes how you can create a database model and what you can do with the model after you create it.

Can't find the database model features?

Not every edition of Microsoft Visio has the database model feature. If you cannot find the features described in the procedures in this article, most likely you have an edition of Visio that does not include them.

• Microsoft Visio Standard does not include the Database Model Diagram template.

• Microsoft Visio Professional and Premium editions support the reverse engineering features for the Database Model Diagram template (that is, using an existing database to create a model in Visio), but it does not support forward engineering (that is, using a Visio database model to generate SQL code).

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

   Create a new model diagram from scratch

   If you don't have an existing database that you want to use as a starting place, you can start with a blank Database Model and add your own tables and relationships.

   1. Click the File tab.

   2. Click New, click Software and Database, and then double-click Database Model Diagram.

   3. On the Database tab, in the Manage group, click Display Options.

   4. In the Database Document Options dialog box, select the symbol set that you want to use and other table and relationship options, and then click OK.

   Use an existing database as a starting point

   If you have a database that you want to model so that you can understand it better or use it as a starting place for a new model, you can use the Reverse Engineer Wizard to extract the schema, or structure, of the database and build a new model.

   Before you start the wizard:

   1. If you are reverse engineering a Microsoft Excel workbook, before you start the wizard you need to open the workbook and name the group (or range) of cells that contains the column headings. If you want to use more than one worksheet, just name the group of column cells in each worksheet. These ranges are treated like tables in the wizard. For more information about how to name a range of cells, see the topic in your Excel help titled Define named cell references or ranges.

   2. For best results, set your default driver to the target database that you want to reverse engineer before you run the Reverse Engineer Wizard. This step ensures that the wizard maps the native data types correctly and that all the code that is extracted by the wizard is correctly displayed in the Code window.

   3. Click the File tab.

   4. Click New, click Software and Database, and then double-click Database Model Diagram.

   5. On the Database tab, in the Model group, click Reverse Engineer.

   6. On the first screen of the Reverse Engineer Wizard, do the following:

      1. Select the database driver for your database management system (DBMS). If you have not already associated the Visio database driver with a particular ODBC data source, click Setup.

         Note: If you are reverse engineering an Excel worksheet, choose the ODBC Generic Driver.

      2. Select the data source of the database that you are updating. If you have not already created a data source for the existing database, click New to do so now.

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

      3. When you are satisfied with your settings, click Next.

   7. Follow the instructions in any driver-specific dialog boxes.

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

      Note: If you use the ODBC Generic Driver, you may receive an error message that indicates that the reverse engineered information may be incomplete. In most cases, this is not a problem — just click OK and continue with the wizard.

   8. Select the check boxes for the type of information that you want to extract, and then click Next.

      Note: Some items may be unavailable (appear grayed out) because not all database management systems support all the kinds of elements that the wizard can extract.

   9. 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.

      Note: If you are reverse engineering an Excel worksheet and don't see anything in this list, it is likely that you need to name the range of cells that contains the column headings in your worksheet.

   10. If you selected the Stored Procedures check box, select the procedures that you want to extract, or click Select All to extract them all, and then click Next.

   11. Select whether you want the reverse engineered items to be added automatically to the current page.

       Note: You can choose to have the wizard automatically create the drawing, in addition to listing the reverse engineered items in the Tables and Views window. If you decide not to have the drawing created automatically, you can drag the items from the Tables and Views window onto your drawing page to manually assemble the database model.

   12. Review your selections to verify that you are extracting the information that you want, and then click Finish.

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

   Import and refine an existing model

   If you have an existing VisioModeler or PLATINUM ERwin model, you may be able to import it to create a Visio database model diagram. This ability is limited to only VisioModeler 2.0 or later dictionary model (.imd) files and PLATINUM ERwin 2.6, 3.0, and 3.52 .erx files.

   1. Click the File tab.

   2. Click New, click Software and Database, and then double-click Database Model Diagram.

   3. On the Database tab, in the Model group, click Import, and then click the model type.

   4. Type the path and file name for the model that you want to import, or click the Browse button to locate the model file, and then click Open.

   5. In the Import dialog box, click OK.

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

   6. In the Tables and Views window, select the tables that you want to model, and then drag them onto the drawing page.

2. After you create a database model diagram, the work of refining the diagram begins. You can add and customize tables and views, create relationships, and customize columns and data types.

   Tables

   Use the Entity shape to create a table in your diagram.

   1. From either the Entity Relationship or Object Relational stencil, drag an Entity shape onto the drawing.

   2. Double-click the shape to open the Database Properties window.

   3. Under Categories, click Definition and type a name for the table.

   4. Under Categories, click Columns, type a name, and choose a data type.

   5. Select the Req'd check box for columns that can't have null values.

   6. Select the PK (primary key) check box for columns that uniquely identify each row in the database table.

   7. Under Categories, click Indexes, Triggers, Check, or Extended to create these optional elements.

   Columns

   Use the Database Properties window to add or change properties for columns, including data types and primary keys.

   1. Double-click the table in your diagram.

   2. In the Database Properties window, under Categories, click Columns.

   3. Click in the first empty Physical Name cell, and type a name.

   4. 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 type it into the list. For example, you can type decimal(8,2) or char(30).

   5. To prevent null values, select the Req'd check box.

   6. To specify that the column is a primary key, select the PK check box.

   7. To see more column properties in addition to those that appear when you click the Columns category, select the column and then click Edit.

   Relationships

   Relationships use primary and foreign keys to allow databases to match a row in one table with a row in a related table. You can show those relationships in your diagram. In addition, you can set their cardinality (for example, one-to-many) and use either Crow's feet, Relational, or IDEF1X notation to show the cardinality. You can't show many-to-many relationships with any of these notations in the Database Model Diagram template.

   1. Create a relationship between tables:

      1. Make sure that both tables are visible in the diagram. If you reverse engineered the model from an existing database, you may need to drag one or both from the Tables and Views window onto the drawing page.

      2. Double-click the table that you want for the primary key side of the relationship.

      3. In the Database Properties window, under Categories, click Columns.

      4. In the grid, click the column that you want to use to uniquely identify each row in the table, and select the PK check box to set it as the primary key.

      5. From the Object Relational or Entity Relationship stencil, drag a Relationship shape and drop it onto a blank space on the page.

      6. Connect the higher end to the table with the parent table.

      7. Connect the other end to the child table.

         If the second table doesn't already contain a column with the same name as the primary key, the modeler adds it to the second table as a foreign key.

         Note: If relationship lines disappear, on the Database tab, in the Manage group, click Display Options. On the Relationships tab, under Show, select the Relationships check box.

   2. Set the relationship's cardinality:

      1. Double-click the relationship.

      2. In the Database Properties window, under Categories, click Miscellaneous.

      3. Under Cardinality, choose the cardinality that best fits the relationship. For one-to-many relationships, the best choice is either Zero or more or One or more. For one-to-one relationships, the best choice is either Zero or one or Exactly one.

3. To make other refinements to your diagram (such as creating indexes, check clauses, and triggers) you can do the following:

   Create indexes

   Indexes improve the performance, or speed, of your database when you run a query.

   1. Open the database model diagram.

   2. Double-click the table to which you want to add an index, and 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 that 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 the check box to create an index that has a descending sort order.

      The database model diagram is updated.

   Create views

   You can think of a view as a saved query. Views are particularly handy if you need to repeatedly access the same information from multiple tables, or if you want to expose the data to users without letting them change the actual tables.

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

   Set extended properties for tables and views

   Depending on your database management system (DBMS), you may be able to set extended properties for tables or views to determine where they are stored.

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

   Create check clauses

   Use check clauses to ensure that the data that is entered into a column is within a particular range of values. For example, you can create a check clause that requires the data in a column called "Age" to be over 65.

   1. Double-click the table to open the Database Properties window.

   2. Under Categories, click Columns and then click the column that you want to add a check clause to.

   3. Click Edit.

   4. On the Check tab of the Column Properties dialog box, enter the constraints that you want.

      The check clause is added to the Code window under Local code.

   Create stored procedures and user-defined functions

   Use stored procedures and user-defined functions to create packets of code that you can reuse to perform the same actions repeatedly. The major difference between the two is that a user-defined function returns a value, whereas the stored procedure executes code without returning a value.

   1. On the Database tab, in the Show/Hide group, select the Code check box to open the Code window.

   2. Click Global Code and then click New.

   3. On the Properties tab of the Code Editor, click the kind of code that you want to create, and type a name for the code.

   4. On the Body tab, type the code and then click OK.

   Create triggers

   Triggers cause SQL code that you specify in the trigger to run when a particular event occurs in the database.

   1. Double-click the table to open the Database Properties window.

   2. Under Categories, click Triggers, and then click Add.

   3. On the Properties tab, type a name for the trigger.

   4. On the Body tab, type the code and then click OK.

      The trigger is added to the Code window under Local code.

This article describes how you can create a database model and what you can do with the model after you create it. For a quick start, on the File menu, point to New, point to Software and Database, and then click Database Model Diagram.

Can't find the database model features?

Most likely, your edition of Microsoft Office Visio doesn't include the features that you are looking for. To find out which edition of Visio you have, click About Microsoft Office Visio on the Help menu. The name of the edition is in the top line of text in the dialog box.

• Microsoft Office Visio Standard does not include the Database Model Diagram template.

• Microsoft Office Visio Professional supports the reverse engineering features for the Database Model Diagram template (that is, using an existing database to create a model in Visio), but it does not support forward engineering (that is, using a Visio database model to generate SQL code).

• You can find the full suite of database modeling features, including both reverse engineering and forward engineering, in Visio for Enterprise Architects. Visio for Enterprise Architects is included in MSDN Premium Subscription, which is available with Visual Studio Professional and Visual Studio Team System role-based editions.

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

   Create a new model diagram from scratch

   If you don't have an existing database that you want to use as a starting place, you can start with a blank Database Model and add your own tables and relationships.

   1. On the File menu, point to New, point to Software and Database, and then click Database Model Diagram.

   2. On the Database menu, point to Options, and then click Document.

   3. In the Database Document Options dialog box, select the symbol set that you want to use and other table and relationship options, and then click OK.

   Use an existing database as a starting point

   If you have a database that you want to model so that you can understand it better or use it as a starting place for a new model, you can use the Reverse Engineer Wizard to extract the schema, or structure, of the database and build a new model.

   Note: Before you start the wizard:

   1. If you are reverse engineering a Microsoft Office Excel workbook, before you start the wizard you need to open the workbook and name the group (or range) of cells that contains the column headings. If you want to use more than one worksheet, just name the group of column cells in each worksheet. These ranges are treated like tables in the wizard. For more information about how to name a range of cells, see the topic in your Microsoft Office Excel help titled Define named cell references or ranges.

   2. For best results, set your default driver to the target database that you want to reverse engineer before you run the Reverse Engineer Wizard. This step ensures that the wizard maps the native data types correctly and that all the code that is extracted by the wizard is correctly displayed in the Code window.

   3. On the File menu, point to New, point to Software and Database, and then click Database Model Diagram.

   4. On the Database menu, click Reverse Engineer.

   5. On the first screen of the Reverse Engineer Wizard, do the following:

      1. 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 ODBC data source, click Setup.

         Note: If you are reverse engineering an Excel worksheet, choose the ODBC Generic Driver.

      2. Select the data source of the database that you are updating. If you have not already created a data source for the existing database, click New to do so now.

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

      3. When you are satisfied with your settings, click Next.

   6. 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. If your data source is not password protected, just click OK.

      Note: If you use the ODBC Generic Driver, you may receive an error message that indicates that the reverse engineered information may be incomplete. In most cases, this is not a problem — just click OK and continue with the wizard.

   7. Select the check boxes for the type of information that you want to extract, and then click Next.

      Note: Some items may be unavailable (appear grayed out) because not all database management systems support all the kinds of elements that the wizard can extract.

   8. 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.

      Note: If you are reverse engineering an Excel worksheet and don't see anything in this list, it is likely that you need to name the range of cells that contains the column headings in your worksheet.

   9. 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.

   10. Select whether you want the reverse engineered items to be added automatically to the current page.

       Note: You can choose to have the wizard automatically create the drawing, in addition to listing the reverse engineered items in the Tables and Views window. If you decide not to have the drawing created automatically, you can drag the items from the Tables and Views window onto your drawing page to manually assemble the database model.

   11. Review your selections to verify that you are extracting the information that you want, and then click Finish.

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

   Import and refine an existing model

   If you have an existing VisioModeler or PLATINUM ERwin model, you may be able to import it to create a Visio database model diagram. This ability is limited to only VisioModeler 2.0 or later dictionary model (.imd) files and PLATINUM ERwin 2.6, 3.0, and 3.52 .erx files.

   1. On the File menu, point to New, point to Software and 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 that you want to import, or click the Browse button to locate the model file, and then click Open.

   4. In the Import dialog box, click OK.

      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 that you want to model, and then drag them onto the drawing page.

2. After you create a database model diagram, the work of refining the diagram begins. You can add and customize tables and views, create relationships, and customize columns and data types.

   Tables

   Use the Entity shape to create a table in your diagram.

   1. From either the Entity Relationship or Object Relational stencil, drag an Entity shape onto the drawing.

   2. Double-click the shape to open the Database Properties window.

   3. Under Categories, click Definition and type a name for the table.

   4. Under Categories, click Columns, type a name, and choose a data type.

   5. Select the Req'd check box for columns that can't have null values.

   6. Select the PK (primary key) check box for columns that uniquely identify each row in the database table.

   7. Under Categories, click Indexes, Triggers, Check, or Extended to create these optional elements.

   Columns

   Use the Database Properties window to add or change properties for columns, including data types and primary keys.

   1. Double-click the table in your diagram.

   2. In the Database Properties window, under Categories, click Columns.

   3. Click in the first empty Physical name cell, and type a name.

   4. 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 type it into the list. For example, you can type decimal(8,2) or char(30).

   5. To prevent null values, select the Req'd check box.

   6. To specify that the column is a primary key, select the PK check box.

   7. To see more column properties in addition to those that appear when you click the Columns category, select the column and then click Edit.

   Relationships

   Relationships use primary and foreign keys to allow databases to match a row in one table with a row in a related table. You can show those relationships in your diagram. In addition, you can set their cardinality (for example, one-to-many) and use either Crow's feet, Relational, or IDEF1X notation to show the cardinality. You can't show many-to-many relationships with any of these notations in the Database Model Diagram template.

   1. Create a relationship between tables:

      1. Make sure that both tables are visible in the diagram. If you reverse engineered the model from an existing database, you may need to drag one or both from the Tables and Views window onto the drawing page.

      2. Double-click the table that you want for the primary key side of the relationship.

      3. In the Database Properties window, under Categories, click Columns.

      4. In the grid, click the column that you want to use to uniquely identify each row in the table, and select the PK check box to set it as the primary key.

      5. From the Object Relational or Entity Relationship stencil, drag a Relationship shape and drop it onto a blank space on the page.

      6. Connect the higher end to the table with the parent table.

      7. Connect the other end to the child table.

         If the second table doesn't already contain a column with the same name as the primary key, the modeler adds it to the second table as a foreign key.

         Note: If relationship lines disappear, on the Database menu, point to Options, and then click Document. On the Relationships tab, under Show, select the Relationships check box.

   2. Set the relationship's cardinality:

      1. Double-click the relationship.

      2. In the Database Properties window, under Categories, click Miscellaneous.

      3. Under Cardinality, choose the cardinality that best fits the relationship. For one-to-many relationships, the best choice is either Zero or more or One or more. For one-to-one relationships, the best choice is either Zero or one or Exactly one.

3. To make other refinements to your diagram (such as creating indexes, check clauses, and triggers) you can do the following:

   Create indexes

   Indexes improve the performance, or speed, of your database when you run a query.

   1. Open the database model diagram.

   2. Double-click the table to which you want to add an index, and 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 that 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 the check box to create an index that has a descending sort order.

      The database model diagram is updated.

   Create views

   You can think of a view as a saved query. Views are particularly handy if you need to repeatedly access the same information from multiple tables, or if you want to expose the data to users without letting them change the actual tables.

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

   Set extended properties for tables and views

   Depending on your database management system (DBMS), you may be able to set extended properties for tables or views to determine where they are stored.

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

   Create check clauses

   Use check clauses to ensure that the data that is entered into a column is within a particular range of values. For example, you can create a check clause that requires the data in a column called "Age" to be over 65.

   1. Double-click the table to open the Database Properties window.

   2. Under Categories, click Columns and then click the column that you want to add a check clause to.

   3. Click Edit.

   4. On the Check tab of the Column Properties dialog box, enter the constraints that you want. For more information about your choices, see Column and Field Properties dialog box (Check tab).

      The check clause are added to the Code window under Local code.

   Create stored procedures and user-defined functions

   Use stored procedures and user-defined functions to create packets of code that you can reuse to perform the same actions repeatedly. The major difference between the two is that a user-defined function returns a value, whereas the stored procedure executes code without returning a value.

   1. On the Database menu, point to View and then click Code to open the Code window.

   2. Click Global Code and then click New.

   3. On the Properties tab of the Code Editor, click the kind of code that you want to create, and type a name for the code.

   4. On the Body tab, type the code and then click OK.

   Create triggers

   Triggers cause SQL code that you specify in the trigger to run when a particular event occurs in the database.

   1. Double-click the table to open the Database Properties window.

   2. Under Categories, click Triggers, and then click Add.

   3. On the Properties tab, type a name for the trigger.

   4. On the Body tab, type the code and then click OK.

      The trigger is added to the Code window under Local code.