From Dwayne Wright - Certified FileMaker 9 Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
CHAPTER 05: About Relational Design
The idea behind FileMaker join tables is that you have two (or more) table occurrences that are linked together via a middle table occurrence. Because FileMaker relationships can flow from one table occurrence, through another table occurrence to a table occurrence on the other side, you can have an almost unlimited number of interlocking relationship combinations.
The join table is designed to support many to many relationships. In a many to many relationship a collection of records in one table occurrence can share a relationship to many records in another table occurrence. Here I have a few illustrations that might help.
EXAMPLE - A car mechanic may have worked on many cars and a car may have been worked on by many mechanics. So if you wanted all the cars a mechanic has worked on and all the mechanics that have worked on all those cars, you might have an impressive list.
EXAMPLE - A movie may have many actors and each actor may have been in many movies. So if you wanted a list of all the actors in a movie and then a list of all the movies those actors had been in, you would probably have a large list.
EXAMPLE - A high school teacher may have many students and each student may have many teachers. So if said you wanted to find all the students for a particular teacher and all of their associated teachers ... you would get a very big list.
So a join table will likely have a primary key field but it isn’t used for much. The power comes from the collection of foreign key fields it contains and how they interact with the data. Normally, a join table will have a foreign key field for each table it links, to support the join operation. When you are talking about a join table setup, it is not uncommon to call the central table the join ... obviously .. and refer to the linked table occurrences as the outer tables. This is because they circle the central join table in an outer orbit.
Here you can see a classic join situation where I have campaigns, clients and staff table occurrences linked together. Notice that I have client, campaign and staff foreign key fields in the table. Using this way, I have a large many to many relationship opportunity. From a staff member record, I can see all the campaigns they are linked to and see what clients might be linked to those campaigns (the reverse is true as well).
In fact, the above setup would even support the ability to have the same staff member assigned to the same campaign multiple times! This may come in handy if the staff member is performing multiple roles within the campaign.
Because FileMaker table occurrences can see each other through the join, when you drag a field into a portal, you can use a field from a table occurrence other than the one defined for the portal! The portal shows you the join records but the field in the portal row can be from the table occurrence on the other side of the join and can show you the appropriate data through the join.
LIST OF WAYS I’VE USED JOIN TABLES
Here is a quick list of some of the ways I’ve used join tables ...
- Invoice Line Items, linking invoice records to inventory records
- Line Items, shared by invoices & purchase orders linking to inventory records
- Phone Number, linked by clients, leads and vendors
- Payments made, to support one payment to one invoice, one payment to many invoices, one invoice with many payments or even payments that have not been applied to an invoice yet.
The most classic example of a database join is a products sold table / table occurrence. This TO (table occurrence) would reside between and invoice TO and an inventory TO. It would hold all the unique line items sold on an invoice. It would join the Invoice TO and the Inventory TO together in ways that could not be done directly between the two files ( or at least not done easily ).
Here are some links to other posts that might be of interest in regards to this topic...
Join Relationship
EXAMPLE: Join Relationship
EXAMPLE: Indirect Related Data Via A Join
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.
© 2009 - Dwayne Wright - dwaynewright.com
The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.
ADVERTISEMENT ==================
Help support this blog by considering a donating to its ongoing growth. For more details, please visit http://www.dwaynewright.com/donate.html
Thursday, August 27, 2009 at 01:21PM 
