Perhaps one of the most asked questions that I see on the Cafe Forum over at FileMaker Today, is “How do I do 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 word 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:
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:
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:
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:
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, let’s 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:
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:
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 I’ll do that.
Phew, glad that is over. I’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.
Stay tuned for Part Two coming very soon…
0 Responses to “Join my Table? – Part One”