What is a Relation Anyway?
I often encounter frustrated new Access users, confused because
they don't truly understand how to set up the tables in their
database correctly. They treat the tables as if they are
spreadsheets, and their fields go on and on and on.
In this issue we'll continue our normalization discussion and
tackle the issue of joins. When you join tables, you establish a
relationship between the data in different tables. Joins come in
- One-to-many and
|One-to-One and a Word about Joins|
Joins are ways to make tables talk to each other. They
establish the nature of the relationship between the data in
the tables. The tables are joined via their key fields,
usually the primary key, which in most cases should be an auto
number (To see a full discussion of primary keys see the June
2004 issue "Keys Are Critical")
A one-to-one join represents a situation where there is one
record in Table A for every record in Table B. This is a
situation that doesn't occur very often, so we won't spend
much time here. You might use a one-to-one relationship to
divide a table with many fields, to isolate parts of a table
for security reasons, or to store information that applies
only to a subset of the main table.
For instance, let's say that we are setting up a database
for an elementary school. Our database contains information
about teachers, students and classrooms.
We would certainly have a table for each of these items. If
a single teacher is permanently assigned to a single
classroom, we would represent this as a one- to-one
relationship, as shown in the picture below.
Note that purists will say that you can merge these two
tables into one. However, it's not something I'd recommend
because if the world changes (and I find that it regularly
does), then you might have more than one teacher assigned to
the same classroom. If you have two tables to start with then
life is easier when you need to make the change. Remember, the
key to good database design is to build in the ability to
handle as many situations as possible. Establishing the
correct relationship between tables is a one of the most
important steps in this process.
One-to-Many is the most common type of join in most
databases. An example of this is elementary school students
assigned to their teachers. For each teacher, there are many
students. For each student, there is exactly one teacher. This
is shown in the picture below.
The caveat here is that a single student may have no more
than one teacher at a time. To represent this relationship, we
put the TeacherKey field in the student table. Note that the
TeacherKey, which appears in both tables, is considered a
foreign key in the student table; it ties the records in both
tables together. With the use of this key, any information
about a teacher can be derived by a query linking the student
and teacher. For instance, if you want a student report that
includes the teacher's name and degree, you would base it on a
query that includes the Teacher table. Since the Teacher key
is in the Student table, any information about the teacher is
available to data about the student. This is an important
concept in relational databases and we'll review the issue in
more depth in a coming newsletter.
With referential integrity turned on (we'll talk more about
that in a future newsletter as well), you get the infinity
symbol next to the Student table and the number 1 next to the
Teacher table. This indicates that there are many records in
the Student table (an infinite amount) for each record in the
Teacher table, which correctly represents our scenario. Each
teacher can have many students.
Note that if you set the link up in the relationship
window, (Tools|Relationships from the database window)
something I recommend, when you go to do the join in your
query, the link will show up automatically.
This is the trickiest relationship to understand and set
up. Let's consider the case of a typical high school, where
each teacher has many students and each student has many
teachers during the course of the day. This is a situation
that at first glance may seem difficult to represent. I often
see it solved as in the picture below:
There are a couple of serious problems with this approach
that could cause trouble down the road. First, eventually a
student may have more than four teachers, in which case the
tables, forms, and reports must be redesigned. Second,
exploiting the power of the database to analyze this data is
more difficult than it needs to be.
The solution to these problems is to establish the correct
relationship between the tables to accurately represent the
situation at the onset. What we need to do is design the
tables so that one teacher can have many students, while at
the same time one student can have many teachers. If this
sounds like a pair of one-to-many relationships, you're right!
A many-to-many relationship is basically two one-to- many
relationships joined together in a common table. A
many-to-many relationship is illustrated below.
This in-between table (if you're a naming purist, it starts
with trel - see the July
2004 issue for more about naming conventions) has two
foreign keys, one from each of the many-to-many tables. The
name trel stands for table relationship; it's a table
containing data about relationships.
Each row in this trel table represents a combination of
student and teacher, and assigns a unique key to that
relationship (StudentTeacherKey). The student key in the trel
table points to a record in the student table, and the teacher
key in the trel table points to a record in the teacher table.
If you run analysis against this data, you can get all
teachers assigned to a student as well as all students
assigned to a teacher. You can also answer questions like:
which teacher has the most students, which teachers have no
Conclusion In most cases, the data you'll deal with
will require establishment of one-to-many or many-to-many
relationships between tables. It is important to think of all
the possible scenarios when you are designing a database and
set the tables and joins (relationships) correctly. Represent
the one-to-many relationships with the foreign key from the
"one" table in the table that represents the "many" entities.
For many-to- many relationships, use a third (trel) table to
turn the many-to-many relationship into two one-to-many
When you take this approach, your database will remain
flexible to handle future changes and you'll also be able to
do efficient analysis when the time comes.
|Shameless Plug Department|
I'll be speaking at the October meeting of the Boston
Area Microsoft® Access User's Group on October 20 at 6:30.
I'll be presenting Self Updating Combo boxes, the subject of
Newsletter. If you're in eastern Massachusetts and would
like to learn more about how to create this tool, come to the
meeting and be sure to say hello.
Tip of the Month - Get Rid of Annoying Multiple Access
Windows in Task Bar
If you find that you have an icon in your task bar for
every open form or report and you'd like them to be
represented by a single icon, you need to turn the "Multiple
Windows in Taskbar" off. In Access 97 and before, a single
icon was the standard. From Access 2000 on, multiple icons
became the default and many users find it annoying.
To eliminate these multiple icons, go to the database
window (F11) choose Tools from the Menu at the top of the
screen. Choose the View tab, and uncheck "Windows in Taskbar."
This will eliminate the multiple icons and you'll have a less