Reverse engineer or design a database with Microsoft Visio
How to use Microsoft Visio for Enterprise Architects SP-2 to reverse engineer your database to get nice drawings, and how to turn nice drawings into actual database objects.
Part 1: Reverse Engineer your database
First up you simply open Visio and create a new Database Model Diagram
This gives you a database menu option in the menu bar with several helpful commands. There are other database modeling types like ER diagrams and ORM diagrams, but those don’t allow you to make changes to your diagram and update the database or generate new databases later. Now that you have your new, blank document we can use the Reverse Engineer Wizard to connect to a database and generate our drawing automatically. On the menu bar, select
Database…
Reverse Engineer to launch the wizard. The first step in the Reverse Engineer Wizard is to use your ODBC data sources to find the database. If you don’t already have an ODBC connection for your database, you can create a new one inside of the wizard. Use the screenshots at the bottom of this article to guide you through creating a new User Data Source for use with Visio.
Once you have selected your database connection object, pick which types to reverse engineer (tables, keys, indexes, etc) and then pick the objects you want to reverse engineer. You are first presented with a checkbox list of tables and views followed by a checkbox list of stored procedures. Finally you are given the option to allow the system to add the shapes to the page or to add the shapes manually. I recommend allowing the system to do the work. The last screen you are presented is basically a review of your selections. Click Finish to have the system design the drawing. The resulting drawing will have all the primary keys, foreign keys, relationships, etc. When you click on an object such as a a database table you’ll get the ability to look into the table’s design; column names, indexes, triggers, etc. Here is an example of just such a view.
Part 2: Generate a database from a drawing
Now let’s take a look at things from the other side. In my particular case I felt that I needed to start from scratch to fully redesign my database, so I began with a blank Database Model Diagram and built up each table individually. To design a new table, drag an Entity object from the Shapes toolbox onto the drawing page and modify its database properties as I have done in this screenshot.
To create a relationship between two tables, drag a relationship object from the Shapes toolbox onto the drawing page. Drag the arrow end onto the parent table and the other end onto the other table.
Visio will attempt to automatically identify the relationship between your tables based on the conceptual names of your tables’ fields. This example shows just such a relationship
I worked on my table and relationship details until I had this design. I was able to normalize the data, reuse some existing tables, and basically reform the data architecture underneath this application to something that seemed to be a lot more reasonable. Now, with this kind of a table layout, I’m properly positioned to use custom business objects or an OR Mapper like the one from Paul Wilson. The old architecture made that choice difficult if not impossible.
Now that I have my nice new database object design, I want to create these tables. Actually, what I really want is the DDL script I can edit and use in any database to generate these tables. In my case, some of the tables represented in my drawing pre-exist and don’t need to be changed in any way. To do this, I go back to the handy Database menu and select
Generate from the list. The first thing that will happen is that the model will be checked for any errors and warnings – things like similar field names with different data types or mismatched relationships. If I have any errors I must fix them first, much like in Visual Studio.
Once I pass the error checking, I am presented with the Generate Wizard – . The Generate wizard allows me to chose a file location for a DDL script or I can actually have Visio connect to the database engine for me and create a brand new database. Chose to generate the script and click next to get to the database specification portion. Here you can pick which type of database rules you’re going to follow, SQL Server, Access, Oracle, DB2, etc. Pick SQL Server, give your database a name for use in the script and click Next.
You will be presented with a review of everything you are about to export. Press next to do an automatic physical review of your model against that database’s rules. Once the validation is completed you can press Finish to generate the DDL file. Even though you might not have selected to generate the new database, you might be presented with a SQL Server Create Database dialog. Click Close on this dialog to get to the option to view the generated DDL script in the Visio code editor. Here is a screenshot of that generated code:
Now that you have this code file you can modify it and run it in query analyzer (yes – I’m still running SQL 2000) against any database you like. The best part is that you can use a tool like Visio to maintain your database. You can easily reverse engineer a subset of tables in a database, check on relationships, etc. You can make modifications and persist those changes back to the database. This is an easy way to learn about your database at a glance. There is an Update command on the Visio database menu that I didn’t go into. This command allows you do generate change scripts, detect differences, log errors, etc. All in all, Visio can be an important tool in your toolbox.
