Skip to main content

Best practice for junction tables

  • April 7, 2021
  • 1 reply
  • 8 views
D

We are using Airtable to track electrical construction projects (PROJECTS table), project clients and vendors (COMPANIES table), who worked on the project (PEOPLE table) and project photos (PHOTOS table). I have a junction table (PROJECT TICKETS) between PEOPLE and PROJECTS because this is many-to-many. PROJECT TICKETS also houses what the persons position was on the project, since this may change from project to project. The PEOPLE table also connects directly to the COMPANIES table since each person can only work for one company at a time. COMPANIES also links directly to PROJECT. The PROJECT table has separate fields for Client, Owner, General Contractor, Architect, Engineering Firm, each of which only hold one link to a record in the COMPANIES table. (Sorry for the long wind-up.)
Here’s my questions:

  1. Should I be using a junction table to connect PROJECTS with COMPANIES and store the label of Client, Owner, General Contractor, Architect, Engineering Firm as a single select field type at the junction record level?
  2. What are the pros and cons of each method?

I
  • inscript
  • Participating Frequently
  • April 9, 2021

Generally, you would want to use a junction table when your linked data changes (semi-)regularly, and when you want to have those changes reflected in the table that contains those links. I think of junction tables when I need editable data associated with a record in an external table to be pulled into the current table. In other words, if you have records that need a field to be filled based on many-to-one specified record in another table, then a junction table would be appropriate. I always think of pay rates at a company (Gareth’s example). In instances where you can allow for one table in your workflow (i.e. Projects) to reference fields from all other tables (Tickets, People, Companies, etc., the better time you will have understanding the relationships between those entities. In your case, it sounds like you want to prioritize using lookup fields (table->table) over junction tables (table->table->table).

In the practical case of #1, you could try having each role be a field in your companies table (that links to your people table), and inserting a lookup for each role in your project table that links to your companies table, so when you select a company in a record in your projects table, their employees show up in each role.

Reply


Most helpful members this week

Terms & Conditions