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 students, etc.

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 relationships.

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.

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 the August 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.