Designing a Database with Microsoft Visio 2000
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.
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
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.
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.