Join My Table Pt 2 - Building the Example Solution

This implementation assumes you have a basic concept of relational database theory, in particular the use of primary and foreign keys in tables.

As a brief catchup, a primary key is a field in a table which has a unique value for each record. A perfect example would be a serial number field, which has a different number for each record. As good database practice, every table in your solution should have a primary key field, regardless of whether it is used or not.

A foreign key on the other hand, is a field in a table, which contains the primary key value of a record from another table. This is how two records in different tables are linked/related to one another, and indeed is how we are going to link an ENROLLMENT to both a STUDENT and a COURSE.

Table Setup

This solution is going to require the three tables mentioned above. It is very straightforward to create the tables. Each table will require a primary key, plus whatever fields you wish to capture information for, as a very basic example:

STUDENTS:

• Student ID (Primary key)
• First Name
• Last Name
• Phone Number

COURSES:

• Course ID (Primary key)
• Course Name
• Location
• Start Time

ENROLLMENTS:

• Enrollment ID (Primary key)
• Enrollment Date
• Fee
• Grade
• Student ID (Foreign key)=
• Course ID (Foreign key)

You can see that ENROLLMENTS has two foreign key fields, because we wish to relate an enrollment record to both a STUDENT record and a COURSE record.

Relationship Setup

How you setup your relationship graph is dependent upon personal preference. In fact, you can successfully implement this solution without having any relationships between the tables at all. However if you want to be able to see enrollments for either a student or course perspective on your layouts, you will need relationships.

In this example I will setup the most simplest of relationships. I have related ENROLLMENTS to both STUDENTS and COURSES via the key field associations as mentioned in the above section.

Enrollments::Student ID = Students::Student ID
Enrollments::Course ID = Courses::Course ID

Building a Course Layout

This solution is going to require layouts for data entry. It will most likely have a Students layout, a Courses layout, and an Enrollments layout.

For this section I will focus on the Course layout only. Adding course-specific fields to this layout is easy. The layout is based on the Courses table occurrence, and so you can place course fields straight onto it.

A useful thing to have on this layout would be a list of all the enrollments for the current course. This can be achieved by placing a portal on the layout. The portal will be based on the Enrollments table occurrence. Be sure to select the related Enrollments table occurrence the one that has the relationship between itself and the Courses table occurrence.

The fields you will want to put in the portal are basically all the fields from the Enrollments with the exception of a couple. First, it makes little sense to put Enrollment ID in the portal unless you are using this to identify your enrollments. Second, putting Course ID into the portal is redundant, since we are guaranteed that every record that displays in the portal will have the same Course ID as the parent course record, the relationship defines this condition.

I have pre-populated all tables with a few records to illustrate how this will look. You can see here that the course has three enrollments.

This layout is fine for viewing course enrollments, but more often than not users wish to directly add enrollments from this layout. The easiest way to allow this is to turn on the Allow creation of records in this table via this relationship option on the relationship dialog. You will want to turn it on for the Enrollments side of the relationship, since it is these records you want to create.

Now, enrollment records can be created directly from the Courses layout by entering information into the last portal row.

While this is a fully workable solution so far, it lacks many nice features which make it usable for the average user. Current limitations include:

• User must know a Students ID to add an enrollment
• No visible way to delete enrollments
• No restriction on number of enrollments created

This solution can be taken much further with a few simple techniques, some of which will be discussed in part three to this article coming soon!

Example File:

Please find attached an example file. This file was used for all the screenshots in this article, and the other two parts to the "Join My Table" series of articles. It is fully commented to help you fully understand what is going on in the file.

Download Example File