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.
From the Ribbon, select the Database tab and click on Reverse Engineer to start the Wizard.
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.
Click the objects that you want to appear in the Database Diagram. Here I have selected just Tables with the Keys and Indexes.
Select the tables that you would like in the diagram from the list.
Review the tables that you selected in the previous step. If you are done, click Finish to generate the diagram.
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.
The complete Reverse Engineer diagram generated by the Wizard.
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.
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.
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.
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.
The import into the AdventureWorks database shows that the column names are the names of the Shape, not the field names of the 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.
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.
The script pops up and can be copied into a Query window in the database.
