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.
