Join My Table Pt 1 - Intro

Perhaps one of the most asked questions that I see asked here on the FileMaker Cafe is in regards to join tables. While some users have an understanding of what they are, they may lack the FileMaker skills to implement them. Others are unfamiliar with the concept altogether, but will explain their real world example. Usually it is a prime candidate for a join table. This article provides a real world example that will give you an understanding of why join tables are important, and in what circumstances they should be used. The example will then be implemented in a simple FileMaker solution.

Let™s get started

Ponder this real world scenario:

œJoe want™s to build a database to manage the computer courses he offers to senior citizens. Being a one-man business, he only offers five courses. He wishes to capture information on his students, his courses, and who has enrolled in which courses."

This scenario is very easy to understand when reading in plain english. Now lets deconstruct this scenario further.

When building a database from a users specification, one of the first things you should do is identify the potential tables:

• Joe wants to record info about his students, so we will need a STUDENTS table.
• Joe wants to record info about his courses, so we will need a COURSES table.
• Joe wants to record who has enrolled, so we will need an ENROLLMENTS table.

With the main tables identified, can you begin to spot what might be the join table? Perhaps one last step will make it more apparent. Lets define in more specific terms which each table will capture within a single record:

• STUDENTS: Information about a particular student.
• COURSES: Information about a particular course
• ENROLLMENTS: Information about a particular student AND a particular course

How about now? Can you spot the join table? I think it is time to let the cat out of the bag. A join table is a tool by which you link two other tables together, or to put it in more defined terms:

A join table is used to establish a many to many relationship between two tables.

What does many to many mean? Well, in terms of the scenario it means this:

• A student can take MANY courses
• A course can be taken by MANY students

By using a join table, you are giving yourself much more freedom to capture as much information as you want, without compromising the integrity of your other tables. The STUDENTS table should only capture information about the student, such as their contact details. Likewise, the COURSES table should only capture information specific to a course, such as it'™s name, location, and start time.

An ENROLLMENT is a thing, it's a tangible, legitimate thing that you will want to capture information about, so it should have its own table.. It will capture who the student is, and which course they are enrolling in. In addition to that, it could capture information such as:

• Enrollment Date
• Fee
• Grade

This information only makes sense if it is pertaining to a person for a specific course a grade is meaningless to a course if you don't know whose grade it is. Alternatively a grade is meaningless to a student if you don't know for which course it was obtained.

Please Do Not Try This At Home:

So why not capture this enrollment information on the students table, and save us the extra table?

Doing this is bad. It is so bad in fact, that words cannot describe that badness it brings, on a badosity meter it would be off the charts. If someone were to try it, lets see how it would go.

Right, so we want to capture enrollment information on the Students table. Well, let™s start by capturing information about the first enrollment a student takes in a course. We will probably need some fields to capture this information:

• Course ID (foreign key)
• Enrollment Date
• Fee
• Grade.

So far so good. Oh look, Peter Jones just enrolled œJoin Tables for Dummies which has a Course ID of 5. So I will now go to Peter Jones record, and fill in my information:

• Course ID = 5
• Enrollment Date = 11/06/2009
• Fee = 25.00
• Grade =

Great. I have successfully captured all the enrollment information I need for Peter Jones enrolling in this course. When the course ends, I can fill in his Grade and all will be good: But wait.. Peter also wants to enroll in Bad Database Design. Where am I going to record THAT enrollment information? Well lets see, I could just create more fields to capture it, so a™ll do that.

• Course ID 2 = 10
• Enrollment Date 2 = 11/06/2009
• Fee 2 = 50.00
• Grade 2 = C

Phew, glad that is over. I ha™ve still managed to capture information on his two enrollments:

6 Months Later:

WHAT?? Peter wants to enroll AGAIN in MORE COURSES??? What am I going to do?? I know: MORE AND MORE FIELDS !!!!!

Wrong.

Already this method has gone down the toilet. I am making the problem much worse by adding a new set of fields to the table for every enrollment. Every time a student enrolls in a course, the database schema must be modified. In short, avoid this like the plague. You don't want to know what kind of problems you will get into when you want to start running reports! Some people who have tried this method have also gone the way of repeating fields. Rather than 1 field per enrollment, they create 1 field, with many repetitions one repetition per enrollment. This is just as bad.

Please see part two where I begin to build a system making use of join tables for this example.

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