Join my Table? – Part Two

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

Students

COURSES

Course ID (Primary key)

Course Name

Location

Start Time

Courses

ENROLLMENTS

Enrollment ID (Primary key)

Enrollment Date

Fee

Grade

Student ID (Foreign key)

Course ID (Foreign key)

Enrollments

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

TO Setup

 

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.

So a basic layout might look like this:

Basic Layout

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.

Enabling Creation

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!