Data Modeling with Access and Visio
Scott McManus
The latest version of Visio—Visio 2002 for Access Database
Developers—provides a data modeling and documentation tool for
Access database developers. The most exciting features of Visio 2002
handle some of the most mundane (and perhaps neglected) parts of
Access database development: documentation and design. Scott McManus
is your guide.
Access developers who model data, or document their data
structure, often have a two-step process:
- Model and document, using one set of software tools.
- Re-create the resulting data structure, using another tool
(in our case, Microsoft Access).
Originally, Visio worked this way; data modeling in Visio was
purely "drawing only." You could draw whatever you wanted, but those
drawings had no direct connection to your development tools. But now
connectivity between Visio models and Access databases makes Visio
more than just a "dumb" drawing tool.
Working with a modeling tool
A useful modeling tool would allow you to generate your database
from the model that you create. Many modeling tools do this by
supporting, at the very least, the DDL (Database Design Language)
portions of SQL. When you're finished drawing your data model, you
can generate a DDL script that can be exported to a text file. If
you want, you can then open that file and enhance it by manipulating
its contents to create a mixture of SQL, DAO, or ADO code that will
create a new database that reflects the design in your data model.
In addition, your model provides excellent documentation about the
structure of your database—at the time that it was created.
This is great when you're creating a database, but,
unfortunately, that's not the end of the story. When business needs
or data requirements change (and they will!), the database will need
to be remodeled, re-documented, and re-created. If your data
modeling tool doesn't support this, you're forced to begin again at
Step 1: Model and document. If you avoid that step by bypassing the
model, the resulting manual updates can lead to inconsistencies
between the design, the documentation, and the database. These
differences may only be slight (say, the names of attributes and
entities), but when someone notices those discrepancies it looks
untidy and unprofessional.
The latest version of Visio provides a data modeling and
documentation tool for Access database developers. Visio seamlessly
interfaces with Microsoft Access, removing the double and triple
handling during the modeling, documenting, and implementation
phases. Visio can not only generate a database from a data model,
but it can also create a data model from an existing database. As a
result, Visio increases your efficiency when updates are required.
When to use Visio
Before going any further, let me be clear about when you should
consider using Visio. Visio will make your life considerably easier
if any one of these conditions is true:
- The client requires that all contracts that they enter into
be fully specified. If you have a contract to provide a database
for these kinds of clients, then you'll need to provide a data
model and documentation in the "job quote" or as part of the
contract documentation. This documentation provides a reference
point to ensure that both parties are satisfied when contractual
goals have been satisfied (as well as providing a set of
billable milestones).
- The client has a requirement to fully document all facets of
their business to facilitate transparency and allow smooth
transition of new employees. A data model that visually shows
the database, coupled with documentation listing the entities
and attributes with their data types, accompanied by a process
flow model would meet that need.
- The client, designer, and implementer are all separate
parties, and communication of requirements needs to be clear,
concise, and understandable by all.
- The solution requires taking an existing Data Structure,
possibly in a different Database Management System (DBMS),
remodeling it, and then creating a new/modified data structure
in Microsoft Access.
What can Visio do for me?
Basically, Visio supports two activities:
- Reverse engineering an existing database to create a model.
- Creating a new database based on a model.
When first starting a Visio project, you should decide which
Visio Database stencil/template to use (see
Figure 1). In Visio Professional you'll have only three of these
templates, in addition to the Database Model Diagram:
- ER Source Model
- ORM Source Model
- Express-G templates
The ER Source Model allows you to create a new model or reverse
engineer an existing model. The ER model itself does not
allow you to export to a new or existing database. To export a
model, you'll need to use the Database Model template. With this
template you can then update or export to an existing database as
well as refresh an underlying ER Source Model.
Whether you're starting from scratch or reverse engineering, you
should take time to set up some options to display the model in the
format you require. So, after selecting the template to use, it's
worth visiting the options available under the Database menu (see
Figure 2). Probably the most important option available is the
"Relationships" option. Your choice here will depend on your
background and how you choose to model. If you leave the default
setting, the relationship is shown with an arrow pointing to the
table referred to by the relationship. My preference is for "Crow's
feet" and relationships as shown in
Figure 3.
In these figures I've used the ubiquitous Northwind.mdb to
demonstrate Visio's relationship modeling conventions. The foreign
key in the Orders table is displayed with the arrow pointing towards
the "Shippers" entity.
Figure 3 also shows the default options for displaying
relationships, and primary and foreign key indexes.
Reverse engineering
When reverse engineering a database, you need to select the database
driver for the database you want to extract information from. This
is done using a Data Source Name. You must have the necessary
security permissions to reverse engineer the selected database. For
instance, you must be able to supply the Administrator user name and
password. This is important! The user name you supply must own all
objects that you select to import. If the user ID doesn't own those
objects, you'll receive an error and Visio won't import any of the
database structures—including any tables that you do have permission
for. If someone has used several accounts to create the database
objects, you'll need to use the Access Security wizard to reassign
ownership to all of the objects.
Once the driver is selected and security is set up, it's a simple
matter to import the tables, views, relationships, indexes, and keys
into Visio. You have the option to display the entities (tables)
immediately, or add them into the drawing area later as required.
You can now modify your design by adding tables, modifying
tables, adding stored procedures or triggers, adding fields,
changing data types, or changing the size of fields. The amount of
material preserved through the import/export process is impressive.
For instance, many Access developers use the Description property on
many Access objects to document their designs. This is preserved in
the Reverse Engineering and Export/Create new processes in Visio. A
quick look at an imported database will show that any objects with a
description assigned to them have that description copied to Visio's
Notes field. The process also works in reverse. As an example, I
selected the Shippers entity and added a field called Contact. In
the table viewer/editor I added the note "Contact at shipping" (see
Figure 4).
Figure 5 shows a database created from the reverse engineered
Northwind database with the updated Description property for the
Contact attribute of the Shippers table.
Once you're happy with your new database design, you can check it
for inconsistencies by running Visio's validation process. If you've
let Visio know what the final destination DBMS is for your model
during the modeling process, Visio will also apply rules specific to
that DBMS.
Creating your database
Once you've changed the structure of the database and finished
remodeling, you're ready to export the model. From the Database menu
in Visio, it's a simple matter to update your ER Source Model and
then either update an existing database or create a new database. If
you 're updating an existing database with data in it, you'll need
to take extra care to make sure that you don't lose any data.
When you're ready to export, you can export a DDL script that you
can then execute in Access to create your database. Alternatively,
you can also allow Visio to automatically create or modify your
database.
Both the Standard and Professional versions of Visio 2002 can be
obtained on a 30-day trial from the Microsoft Web site for the cost
of postage (see the sidebar "Visio Versions" for more detail on what
each version of Visio will do for you). To obtain demonstration
versions, go to
www.microsoft.com/office/visio (this is also the Microsoft Visio
support site). The Enterprise Architect version is only available
with Visual Studio .NET Enterprise Architect. I did most of the
testing for this article with a trial version of Visio 2002
Professional (the rest was done using the Visual Studio .NET
Enterprise Architect version).
The modeling and documentation tools in Visio 2002 aren't for
every developer. Access does provide basic documentation tools
(though they lack flexibility), and that may be enough for you. FMS
provides the "next level" of documentation tools. However, if you
re-engineer databases, transfer legacy databases into Access, or
need to model your data before implementation, Visio provides an
integrated tool that goes beyond documentation.
Sidebar: Visio Versions
- Visio 2002 Standard—No database tools. Designed as a
drawing aid, useful for flowcharts, diagrams, maps, and
organization charts. Can be automated from Access.
- Visio 2002 Professional—Logical modeling of
Relational and Object databases. Can reverse engineer a database
from most RDBMS formats. Can update an existing model from a
linked database.
- Visio 2002 Enterprise Architect—Can export or create
a new database based on a model. Can create DDL script. Can
export code from the model into either VB.NET or C.NET.
- A new "Visio viewer" has just been released that allows
others to look at your drawings using Internet Explorer 5.x and
above.
To find out more about Smart Access and Pinnacle
Publishing, visit their website at
http://www.pinpub.com/html/main.isx?sub=57
Note: This is not a Microsoft Corporation
website. Microsoft is not responsible for its content.
This article is reproduced from the April 2003
issue of Smart Access. Copyright 2003, by Pinnacle Publishing, Inc.,
unless otherwise noted. All rights are reserved. Smart Access is an
independently produced publication of Pinnacle Publishing, Inc. No
part of this article may be used or reproduced in any fashion
(except in brief quotations used in critical articles and reviews)
without prior consent of Pinnacle Publishing, Inc. To contact
Pinnacle Publishing, Inc., please call 1-800-788-1900. |
