This article explains how to use the Microsoft Access
Relationship Designer to diagram a database. In Microsoft
Access parlance the relationships created between the
tables in the designer are called permanent links. Those
created in the query designer for a query are called
transient links. However, the query designer will
automatically use the links you create in the Relationship
Designer if you have set them up (as we will do in this
article).
Background
First, why would you want to diagram your database? Three
reasons:
- implement referential integrity
- implement
relationships between columns with different names
- show pictorially how the data in your database is organized.
That said, it is not strictly necessary to implement the
relationships in the designer if you are willing to give up
the above benefits.
For our example we will build an Access Relationship Design
for our Factory2000Plus extension database. There are four
tables: employees, employee benefits, work orders and labor.
Along the way we will look at creating both one-to-one and
one-to-many relationships. Lastly, we will experiment with join
properties and removing referential integrity.
Database Schema
The schema can be represented in text with a few
conventions: bold = required field,
underline = primary key, italic
= foreign key.
Employees(EmpNo, fName,
lName, rate, MgrNo)
Employee_Benefits(EmpNo,
insPlan, 401kPlan, savPercent)
Work_Orders(WoNo,
descr, cost)
Labor(EmpNo,
WoNo,
start_date, end_date,
hours)
You can download the database at
box.net.
Creating the Design
Creating the design can be thought of in two steps.
- add the tables to the designer
- link the tables and establish referential integrity
Once we have done that we will go back and make some
changes so that we can cover additional aspects of
relationships.
- modify referential integrity
- modify join properties
Add tables
Open the relationship designer
Click the relationships command on the
database tools tab.
This will open the relationship designer with the show tables
dialogue. The show tables dialogue allows you to display all
the tables or all the queries or both.
Add the tables
Highlight the desired
table and click add. Do this for all four tables. Or you can
shift click to select all four tables at once. You can
also double click the table name to add it.
Relationship Design before creating relationships
When done the relationship designer should look like this.
You can arrange the tables however you like. On the
next page we will create the relationships (permanent links)
between the tables.
Continue to page 2 - create table relationships...
Submit the form below to be notified of new articles or other
Microsoft Access resources made available...
Learn more about our Microsoft Access courses..
|
