Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

Python

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


Database Diagramming Tips

Database Diagramming

This article describes using the Database Model Diagram template in Visio 2010. This template can be used to create a visual diagram of tables, relationships, and keys before implementing them in a database - a great way to see where you can normalize your database before it is created. Also covered is the Reverse Engineer capability of Visio 2010. The Reverse Engineer tool in the Database Model Diagram template is very useful for extracting a Database Schema complete with tables, primary keys, foreign keys, and relationship data out of a database. Lastly, a using the Export to Database Add in to export Table Shapes from the Schema to a database is discussed as well as a Forward Engineer tool from Codeplex. This functionality is no longer available in Visio 2013.

Database Reserve Engineer Tool

To get started, open Visio 2010, select Software and Database from the Template Categories, and select Database Model Diagram from the Templates available.

Database Model Diagram

From the Ribbon, select the Database tab and click on Reverse Engineer to start the Wizard.

Reverse Engineer button

The Wizard appears and you can select the database drivers from the drop down box. Microsoft SQL Server is selected here, but other databases such as DB2, Oracle, MS Access, and generic OLE and ODBC drivers are available. In the Data Sources list box, I have selected the AdventureWorks database from my 2008R2 SQL Server. This tool will not work with later versions of SQL Server such as 2012 or 2014.

Reverse Engineer Wizard

Click the objects that you want to appear in the Database Diagram. Here I have selected just Tables with the Keys and Indexes.

Object Selection

Select the tables that you would like in the diagram from the list.

Table Selection

Review the tables that you selected in the previous step. If you are done, click Finish to generate the diagram.

Finish Wizard

The diagram appears with the tables displaying the Primary and Foreign Keys and the 1 to many relationships between the tables automatically. This image is a close up of the complete diagram to better see the tables.

Partial Reverse Engineer Diagram

The complete Reverse Engineer diagram generated by the Wizard.

Complete Reverse Engineer Diagram

Database Schema

Visio 2010 provides you with shapes to create your own database schema within a Database Model Diagram. Just drag and drop an Entity Shape from the Shapes menu onto the diagram. You can then add columns, set the Primary Key, set the data type, set Check Constraints, create Indexes and Triggers, and more.

Database Table in Diagram

Add another table to the diagram and you can set relationships between the 2. In this example, I have a Category table and a Product table which uses the CatID from Category as a foreign key in Product. I can set the relationship by dragging the relationship shape onto the diagram, setting the cardinality (under Miscellaneous) and then clicking on Definition and clicking Associate to create the foreign key relationship between the 2 tables.

Relationship between 2 tables

Image of the Database Properties window

Export to Database Tool

There is a tool that allows you to export the table shapes to a SQL database. This tool is found in the View tab of the Ribbon under Add Ons.

Export to Database Add On

A Wizard pops up and has a connection to an ODBC data source. I created the new connection by clicking on the Create button to create a User Data Source - the same steps as in creating any DSN. You can see the field names in the Cells to Export are not what you would expect - fields such as FlipX, FlipY, PinX, etc. do not look like the fields that are in the table on the diagram.

Wizard Image of Export Data

The import into the AdventureWorks database shows that the column names are the names of the Shape, not the field names of the table.

Sql Server imported table

Forward Engineer Tool from Codeplex

There is a tool which will forward engineer the diagram of tables into the database. The link to the add in executable at CodePlex is here: http://forwardengineer.codeplex.com. After it is installed, a new tab will appear in the Visio Ribbon called Forward Engineer. Click the tab and select Forward Engineer button.

Visio Forward Engineer

The Forward Engineer wizard appears and allows you to output the SQL to create the tables in the database from the Database Diagram. Click OK and the script appears.

Forward Engineer Wizard

The script pops up and can be copied into a Query window in the database.

SQL from Forward Engineer Tool