Using Junction Tables

We've already worked with one junction table today, tblSections. We will be building another junction table to solve another many-to-many problem. Specifically, we want to be able to assign multiple instructors to a section of a course. Each assigned instructor will have a specific role within the course. For example, we might have a lead instructor, lab instructor, student coordinator, lecturer, etc. This would mean that one faculty member could teach many sections, but one section could have many faculty teaching it. In other words, it would be a many-to-many relationship, so we will need to create a junction table to make it work.

Creating the Sections & Instructors Table

Before we start any work in Access, let's plan out how this table will work. We need to be able to draw a bridge between tblFaculty and tblSections to allow multiple instructors, so we need to consider how to represent this new data within our database.

Currently, we have this relationship:

In order to bridge the two tables and allow multiple records, there are several things we need to consider. First, how will our table keys work? Right now, we have a composite key in tblSections. Can we trust our data entry specialists to be able to correctly carry over the appropriate fields? Can we simplify the relationship?

The table keys should be reworked in these tables. Specifically, we shouldn't trust data entry personnel to be able to reliably enter two key values when we can use an autonumber to make this process easier and less error-prone. This will also reduce redundancy in our database.

Second, if we need to change the key to make this work, can we maintain the integrity of the data in tblSections after changing it? If so, what sort of constraints need to be made on the data to ensure that a section number is only used once per semester?

By creating an auto number primary key, we will lose the built-in guarantee that a section number can only be used once a semester. We will need to change some table properties to retain this restriction. We will explore those properties as we create our junction table.

The third thing to consider is what data needs to remain in tblSections and which pieces can be removed. For example, do we need to maintain InstructorID in tblSections, or can we leave it in our junction table?

Since the relationship will be made between tblSections and the junction table, tblSectionsInstructors, on an autonumber field, we can store the InstructorID information in just tblSectionsInstructors and remove it from tblSections. This helps us keep our data normalized by eliminating the redundant data.

All those things considered and solutions implemented, we would like to end up with a structure similar to this:

Let's start by tackling our first consideration, the key fields in tblSections.

Understanding Composite Keys

Composite keys can sometimes cause problems when expanding a database's functionality. Specifically, in our case, we would have to carry the whole key from tblSections (SectionNumber and SemesterYear) into tblSectionsInstructors. Having to maintain two fields for a relationship can introduce an opportunity for error. Simplifying this into an autonumber field (SectionKey in the previous screen shot) will allow us to reduce the chance for error and redundancy in our data, making it easier to normalize our data.

Converting the Composite Key to an AutoNumber

There are a couple of things we need to do to convert to a different key. The first is to create the new key field. Let's do that now.

Step1. Open tblSections in Design view.

You see tblSections open in Design view:

Let's now add the field above SectionNumber.

Step2. To add a row above SectionNumber, on the Design tab of the Ribbon,

Click

The row that gets inserted is the row that we will make our SectionKey field.

Step3. To add the SectionKey field, in the row that was just added, type:

SectionKey Tab key

The field is created, but we would like to change the data type to autonumber.

Step4. To change the field to autonumber, type:

a Tab key

We've created a new autonumber field called SectionKey. Let's make it the primary key.

Step5. To make SectionKey the primary key for tblSections, on the Ribbon,

Click

The composite key fields are unselected and SectionKey is now our primary key. Let's save our table.

Step6. To save the table, press:

Control key + S

Now that we have the composite key converted to an autonumber field, let's consider our second item, making sure the combination of SectionNumber and SemesterYear are kept unique from row to row.

Creating Unique Constraints

The second thing we need to do is maintain the primary key-like functionality of the SemesterYear and SectionNumber combination. This is accomplished by establishing a unique constraint. A unique constraint is a way for us to mark a field or a combination of fields to be unique throughout the table. Unique constraints are a type of index. Indexes are a table property that allow us to require the data to conform to a particular condition. We've already been working with one other type of index, the primary key, but you can create indexes for non-key fields. There are also unique and required constraints.

Unique constraints are usually applied to candidate keys. A candidate key is a field or combination of fields that could serve as the primary key in a table. Identifying candidate keys is an important step in database design because candidate keys will likely need to have unique constraints applied to them so they function like primary keys. In our example, the combination of SectionNumber and SemesterYear serves as a candidate key. We will apply a unique constraint to make sure they maintain that candidate status.

Step1. To open the Indexes for tblSections, on the Ribbon,

Click

You see the Indexes: tblSections dialog box open:

Notice that the indexes have names, an associated field, and a sort order.

NOTE: CourseCode and InstructorID already have associated Indexes. Access created these indexes when those fields were set to be the foreign key of a relationship.

We want to create a new index that will combine the SectionNumber and SemesterYear fields into a single unique value. First, we need to give the index a name.

Step2. To name the index, in the empty row below PrimaryKey, type:

SectNumSemYr Tab key

In the Field Name column, we'll add the two fields, one below the other. First, let's add SectionNumber.

Step3. To add SectionNumber as a field, type:

SectionN Tab key Tab key Tab key

You should find your cursor in the Field Name column on the next row down. By not giving this row an index name, we are including it as part of the previous index. Let's add SemesterYear now.

Step4. To add SemesterYear to the index, type:

Seme Tab key Tab key

Now, let's set the unique constraint on this index.

NOTE: You can also make these selections using the drop down list for field name.

Step5. To select the index, in the Index Name field,

Click SectNumSemYr

The index is selected:

Now, we will change the Unique property to Yes.

Step6. To change the Unique property to Yes, at the bottom, in the Index Properties section,

Double-Click Unique

You should see the following in the Indexes: tblSections dialog box:

7. Close the Indexes: tblSections dialog box.

Step8. Save and close tblSections.