Designing a Database with Microsoft Visio 2000

Introduction

When researching how to design a relational database almost every article or

book talks about starting with a diagram or drawing to figure out how the

information should be organized. This process can be long and involved, which

does not mean it should not be done, just that if there is an easier way it

should be investigated. There are a number of tools on the market now to

facilitate the design of a database.

An extremely useful tool to reverse engineer a database is Visio 2000. It

also has the ability to generate a database. This generation takes place after

Visio has been used to draw the tables and relationships. This process can be a

huge time saver. If a database needs to be tweaked and reworked it can be done

in Visio and then regenerated. This article is intended to provide a nice

introduction to using Visio 2000 to generate a database.

Getting Started

To get started, open Visio and select new and then database model diagram.

The process of generating a database is straightforward, although there are some

options one should set before starting the design process. First, make sure the

default driver is set to the type of database being created. Visio will allow

one to create a variety of Databases (FoxPro, Access, SQL to name a few). When

the driver is set incorrectly the data types displayed are not necessarily the

data types available for a given database. To set the default database drivers,

first select the database menu item, options and then drivers. Pick the desired

driver and click the set as default button.

An error that can be difficult to detect is duplicate object names. Depending

on the Visio database options when a table is deleted from the diagram it may

not be deleted from the model. Although the tables' windows allow one to see all

the listed tables, relationships can persist when deleted from a drawing. Visio

saves the objects for a drawing regardless of if they are displayed or not.

There is an option to prompt whether an object should be deleted from a model or

not. To set these options go to the database menu, options, modeling. On the

logical diagram tab check the ask the user what to do. Also, check the

ask the user what to do option on the ORM diagram window. If an object is

deleted from a drawing Visio will prompt on deletion and ask whether it should

be removed from the model.

Adding a Table

To add tables to a drawing drag an entity from the entity relationship

toolbar to the drawing page. Once it is on

the page the database properties window will

appear. In this window the table can be named, commented and fields/columns can

be added. To change the column names double click on the column name and edit

the name. The data type can be selected from the drop down list. In most cases

you will need to create a data type if it does not yet exist in the database

model. This is where the default database driver is important. When a new data

type is selected you are given choices based on the selected database driver.

Once the columns have been entered or as you enter each column they can be

edited. From the column tab on the database properties window click edit. The column properties window allows you to update

additional information about a column. This is a good place to add descriptions

to the notes section of the columns.

Adding a Relationship

One of the main reasons to plan the design of a database is to see the

relationships. Once the relationships are planned it makes importing data and

populating the database easier as foreign key checks will be in place. To add a

relationship, select the primary key for each table. In this case the primary keys would be the intTestID field in

each table.

Next drag a relationship object from the entity relationship window on the

left hand side of the Visio work area to the

drawing area. Each end of the relationship arrow has a handle, which can be

dropped on a table. Drag the end with the arrow to the table which has the

required value and then drag the end without the arrow to the table that will

contain the Foreign Key reference to the primary key.

At this point there are two tables with a relationship to one another. The

database properties window displays relationship information pertinent to the

two tables. In this window the referential integrity, the type of relationship

(one to many, etc.) and notes can be added for each relationship.

Error Checking the Model

As the database is being developed it is useful to error check. Running error

check will analyze the variable types, tables and relationships to determine if

there are any problems with the current design. The error check looks at

variable types based on the database driver. To error check select the database

menu item highlight model and then error check. An output window will display

any of the error messages. Once any error messages have been corrected the

database can be generated.

Generating the Database

After error checking it is time to create the database. Clicking on the

database menu item and highlighting generate will start the process. The screen of the wizard asks whether a script

should be created, a database should be created, and if a copy should be stored

with the model. In most cases creating the database and storing a copy of the

model will suffice. The copy of the model is useful for updating the model or

database. The next screen connects Visio to an existing database or allows the

database to be created using an interface resembling the Data Sources

(ODBC) control panel. Clicking next will show the physical tables that will

be created clicking next again will create the database.

Updating the Database

If the "save changes in database" box is checked, the database model and the

database diagram can be synched using the update command. The update command is

under the database menu item. This can be useful if a number of changes have

been made to the model or the database. Also, the database structure can be

updated even if the database contains data. It is good practice to backup both

the model and the database at regular intervals.

Conclusions

Maintaining documentation on projects can be difficult. Tools like Visio that

allow one to build documentation into the intial creation of the database

facilitate the documentation process. After the database is built and updated,

maintaining the documentation using Visio is much easier than trying to use a

spreadsheet and word processor document. Also, the ability to sych between the

database and the documentation program saves a lot of time, especially after

changes are made to a database and the documentation needs to be updated.