Using Visio 2003 for Enterprise Architects to Design SQL Server Databases

• Download Visio File - 65 Kb

Introduction

Although it has many quirks, Visio 2003 for Enterprise Architects (available with MSDN premium and above), is a great tool to use when you're laying out your database design, and can be used to organize your thoughts, annotate tables and views, store stored procedures, and remain in sync with your SQL Server database.

The best bit about it, though, is that although it sounds obvious, you can use multiple pages within the visio diagram.

Background

When planning a recent database project (with an anticipated 30-40 tables), I decided to make use of Visio to lay out my ideas first, rather than using something like Access, or just diving in and creating the tables in SQL Server. 

For this example, I will be using a very simple database design based around a membership database.

I want to record the following entities:

• Members
• Members Notes
• Members Subscriptions received

These entities rougly translate to the tables I will use.  Also, the design is purposely simplistic.

Getting Started

Create a Database Model Diagram in Visio.  Check you have Database > Generate on the menu. If you don't, then you probably have Visio Standard rather than the Enterprise Architect version.

The first thing to do is to let Visio know that you're planning on using SQL Server. On the menu, go to Database > Options > Drivers and select the Microsoft Sql Server driver.

The window is devided into 3 panes: Shapes, the diagram pages, and Database Properties (at the bottom).

Creating the tables

Drag an "Entity" shape onto the diagram page.  In the database properties window, change the Physical name to "Member".

Now click the "Columns" option in the Database Properties pane.

We need to add the following columns, so just type into the boxes provided (starting with Physical Name)

Physical Name   | Data Type     | Req'd |  PK  | Notes
memberID        | int identity  | tick  | tick |
emailAddress    | varchar(200)  | tick  |      |
surname         | varchar(100)  | tick  |      |
forename        | varchar(100)  | tick  |      |

You'll notice that "int identity" and "varchar(200)" aren't in the datatype drop-down list.  That's fine, you just need to type it in,  rather than select from the list.  You can also type in any notes to remind you what a column is for.  This can prove useful at the early stages of design.  Another tip is to use the space bar to tick or untick the tickable fields, and use the tab key to move between boxes.  This makes it quite fast to create columns.

We now have our Customer table created.  On the menu, select Database > View > Tables and Views.  A new pane will appear showing the list of tables that you have in your model.

Now create the following tables with the following columns:

Table Name: Subscription
Physical Name   | Data Type     | Req'd |  PK  | Notes
subscriptionID  | int identity  | tick  |      |
amountDue       | smallmoney    | tick  |      |
datePaid        | smalldatetime |       |      | null if not paid.

Table Name: Note
Physical Name   | Data Type     | Req'd |  PK  | Notes
noteID          | int identity  | tick  | tick |
noteTimestamp   | smalldatetime | tick  |      | (see below).
noteText        | varchar(max)  | tick  |      |

For the noteTimestamp column of the Note table, click the Edit button, and enter get_date() into the default value field, and change the radio button so that "Is an expression or function call" is selected.

We now have 3 tables that we want to link together.

Linking the tables

Drag the relationship connector shape onto the diagram.

Drag the pointy end onto the Member table (let go when the table turns red), drag the blunt end onto the Note table. This will create a new foreign key column in the note table.
Do the same for the subscription table.  The Member table should now be linked to both the Note and Subscription table.
Of course, you can also rename the new memberID column to one of your choosing, or create the memberID column first and associate it with the link later.

Imagine now that we have 30 tables - they would get quite cluttered on one sheet, even if you changed the paper size, so we're going to organise things a bit better.

Using Pages to organise the tables

At the bottom of the diagram it will have a page name of "Page-1" (like worksheet names in Excel).  - Rename this by double clicking it to Notes

Right click that page name and click Insert Page.  Call this one Subscriptions

Go back to the notes page, select the Subscription table and press the delete key.  When it asks if you want to Remove selected item from underlying model? select NO. We've only removed the table from that sheet, it should still be in the Table and Views pane to the left.

Change back to the Subscriptions page, and drag the Member table and Subscription table (both in the Tables an Views pane) onto the blank sheet.
This, in my opinion, is one of the best bits about visio - you can have multiple copies of one table visible on as many sheets as you want. 

If at a later date you decided to add another section, say Meetings to record what meetings the members had attended, you could create a new page or two, drag the member table onto it, and model it without the clutter of the notes and subscriptions related tables.

Annotating etc...

You can now add more visible notes by selecting different shape (File > Shapes > Visio Extras > Callouts for example) and attaching a callout to a table.  It's very useful for reminding yourself 3 months down the line what that table was for.

When you've finished adding various documentation, it's time to create your database.

Creating your database on the server

Visio makes use of ODBC driver connections to connect to your database.  I find it useful to create the database in Enterprise Manager (or Management Studio) first, and set up a DSN in the ODBC settings (found in control panel).  Once you have an empty database with an ODBC connection do the following:

On the menu, click Database > Generate.  Tick Generate new database and untick Generate a text file... . Click Next.

Click Database already exists and click Next. Select your database from the list and click Next - depending upon your authenticathion method, you may need to authenticate yourself to the database.

A summary of tasks that will be performed will now be shown - in this case, it should be creating 3 tables. Click Next and Finish.

Keeping in sync

You can update your model based upon the live database.  Switch to Enterprise Manager (or Management Studio) and add the column dateJoined: smallDateTime to the member table.

Switch back to visio, and change the amountDue column of the Subscription table to amountPaid.

On the menu, select Database > Update.  Tick Update the database and click Next.  Select your database and authenticate if necessary.  Next you will see a summary of changes detected in the live database.  For each item in the list (or selecting the root), select the resolution you would like to choose - in this case, we want to bring the changes into the model, so select the Refresh Model radio button.

Click next a couple more times, and it will show a summary of changes that have been made to the model - these changes will sent back to the database.  Click Finish.

Now your Member table in the model should have a column called dateJoined and in the live database the amountDue column in the Subscription table should now be called amountPaid (you may need to refresh).

Tips and Tricks (and Quirks)

Visio has a number of oddities.

Stored Procedures:

Click Database > View > Code will open a new tab in the Tables and Views pane called Code.  This shows any stored procedures and triggers that have been bought in from the database.  Unfortunately, the Update command (detailed above) doesn't pull in stored procedures that were created in the database, so you need to use the Database > Reverse Engineer menu option.  The Update command does, however, update stored procedures that have been previously Reverse Engineered, or created within the model. 

The reverse engineer command is much like the Update option except that it doesn't send model changes to the database.  Deselect the Tables and Views, and select all Stored Procedures (it will update or add new or updated ones, and ignore unchanged ones).  Any stored procedures will now appear in the Code pane.  No, you can't drag them onto the diagram (annoyingly), but you can edit them by double clicking them.

Creation Order:

The tables and views by default are sorted alphabetically.  You may occasionally receive an error about not being able to create a foreign key table because another table doesn't yet exist.  Right click the tables in Tables and Views remove the tick in Sort.  Then drag the tables into the correct order based upon dependancies (usually just drag the one mentioned in the error so that it's above the one that was being created).

Update command - Multiple Roots:

The update command asks whether you want to Refresh the model or update the database, or do nothing.  Refreshing the model copies the database changes into the model. Updating the database sends the changes in the model to the database.  The screen that asks you what you want to do may show a long list of changes, and the treeview actually has 3 roots.  An obvious mistake that I made was to select the first line (the root of the tree view) and change all sub items to update the database.  When I looked at the database, only the  tables had changed, the views and stored procs hadn't been updated.  This was becuase I should have paged down a bit and set the Views to update the database, and then do the same for stored procs. 

Using it in practice

To get a complete database creation script, you need to start with an empty database, otherwise the database script generated by Visio will only show the changes found during the update.  To achieve this I delete and recreate the database every now and then so that I get an updated create script.  I then attach that script to a database project in a visual studio solution, along with a load of other scripts.

One of those scripts deletes the database and creates it so that it is empty. I then have the DDL script generated by Visio also as part of that database project, so I can delete the database, and rebuild it based upon the model.  I then have other scripts to populate it with test data etc...

The benefit of doing it this way means I can run an msbuild script that will create the database and tables, views, stored procs, populate it with my test data, and then run my nunit tests, all from the Visual Studio command line.  I can go an get a coffee while it runs. :)

If there's interest, I will write an article detailing the msbuild part.

History

Created - 3/Jan/2007.