HINT - Join Tables, Many to Many, Self
Some Google Results
- - - READ THESE - - -
September 30, 2010 / 7 comments
In part two, I demonstrated how to implement the basics of the join table solution in FileMaker. While the solution works fine as an illustration for join tables, it lacked many of the basic interface features that would make it a usable solution.
In part three, I will show how to implement some of these features, namely the following: Better selection of a Student for an Enrollment, Deletion of Enrollments & Prevention of “over-enrolling” a course… Read more...
September 28, 2010 / 2 comments
Part two carries on from where Part one left off. This article outlines the construction of a basic join table solution from woe to go… Read more...
September 27, 2010
Perhaps one of the most asked questions that I see asked 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 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… Read more...
- - - Filemaker Com Links - - -
The Self-Join Relationship: What it is and some examples of how to use it Updated: Sep 20, 2011
Understanding And Creating Many-To-Many Relationships in FileMaker Pro
What do I need to know to work with many-to-many relationships in FileMaker Pro?
Updated: Oct 04, 2011
A relational database consists of data and information organized into different tables. A table is used to define a specific, single group of information, like a table of customers for a business or a table of products that the business sells. Each record in that table should include a “key field.” A key field is a field or combination of fields that uniquely identify a record (row) in a table. A key field can be referred to as a "primary key" and a "foreign key." A "primary key" is a key field that is in the same table as the record it identifies. A "foreign key" is a key field that identifies a record in another table. Understanding the difference between a primary key and a foreign key will be useful when dealing with many-to-many relationships in FileMaker Pro.
Relational database design supports many different types of relationships between tables. These relationships are in place to prevent the entry of inconsistent data and enforce referential integrity. FileMaker Pro supports the following types of relationships:
|One-to-many relationships||The most common relationship used when creating relational databases. A row in a table in a database can be associated with one or (likely) more rows in another table. An example of a one-to-many relationship is a single order has many items on that order. And since relationships work both ways it is not uncommon to hear reference to many-to-one-relationships as well.|
A row in a table is associated to one and only one row in another table. An example of a one-to-one relationship is a person can have one social security number and a social security number can only be assigned to one person.
In most cases there is no need for a one-to-one relationship as the contents of the two tables can be combined into one table.
|Many-to-many relationships||When one or more rows in a table are associated with one or more rows in another table. An example of a many-to-many relationship is a table of customers can purchase many products and many customers can purchase a product.|
In relational database design, a many-to-many relationship is not allowed. Consider the example of keeping track of invoices. If there were “many” invoices with the same invoice number and one of your many customers called up asking about that invoice number; how would you know which specific one they were referring to? The answer is you could not. An invoice should have a unique “ID.”
To get around the problem of having a many-to-many relationship you need to break apart the many-to-many relationship into two one-to-many relationships. Using a third table, commonly called a “join table”, does this. Each record in the “join table” would have the foreign key fields of the two tables it is joining together. Nothing special needs to be done with the foreign key fields in the join table as they will get populated with data from the other two tables as records are created. It is not uncommon for a join table to have a lot of records in it – since records are created in the join table as records are created in the two tables it joins.
How to setup a join table in FileMaker Pro:
For our example we will discuss a typical many-to many-scenario: Students and Classes. We will have a Student table that contains a record for each student and a Classes table that contains a record for each available class. The primary key field StudentID uniquely identifies a student in the student table. Likewise, the primary key field ClassID uniquely identifies a class in the class table.
A student can take many classes and a class can have many students. Related these two tables together would be difficult without the use of a join table. The join table will contain the primary keys from the student table and the class table. The join table can be called anything you want. For this example we will call the join table “signups.”
- Create a new table in FileMaker Pro and call it "Signups"
- In the “SignUps” table, create a primary key field called “SignupsID.” This step is not necessary for the join table to work properly but it is good relational database practice for every table to have a primary key.
- In the "SignUps” table, create, at least, a “StudentID” field and a “ClassID” field. Since these fields will be populated when records in either the “Students” or “Classes” tables are created, no auto-enter field options are needed.
- Additional fields can be added to the “Signups” table as necessary. Some examples of other fields that might be appropriate for the “Signups” table would be a “date” field to keep track of when someone started a class or a “cost” field to keep track of how much someone paid to take a class. Join tables typically hold fields that might not make sense to have in any other table.
- Relate the tables together based on the primary and foreign keys
If a student signed up for three classes, a student would have one record in the Students table but would have three records in the “Signups” table – one for each class he or she signed up for.
A by-product of utilizing a join table is that you are able to access fields and data across tables without having to create a separate relationship. In our example, it would be very easy to display information from the Students table in the Classes table if need be and vice versa.