How to Use a Junction Table in your database | Airtable Tutorial

That is a major discovery for me. I will use this a lot, thanks 👍

Love it! Thank you! :D Haven't figured out my specific use case for junction tables yet, but when the time comes, I know I'll be prepared!

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?

Nice! This strategy will help fix several struggles I've had with my airtable database! Now I just have to dig in and figure out how to adjust/fix my tables with this method. It will also help me with some metrics I've wanted to set-up but couldn't figure out how to do it.

👍🏻 Thanks !

Junction script is a real winner :) Personal preference: I like to use the naming convention "Table 1 x Table 2" for junctions to keep track of the source tables.

This is something SUPER helpful!! Thank you Boss!!

Hmm I definitely feel like this is something I've been missing in my AirTable bases and it's probably the reason why I get stuck a lot of the time. I still am trying to figure out what kind of use cases I can use this for when it comes to my personal task management...Thanks for giving me something to think about!

text

Excellent video and very instructive. Nevertheless this junction table creates records for all possible combinations table 1 x table 2, but what if only some records in table 1 relate to table 2 ? Is there a script for this ?

Hi, thanks for your explanation, I think I'll be using this a lot :-) I've got a question: is a junction table the best way to go if I want to do a price update for item I'm selling? I haven't figured out quite how to do that; when I update the price per item, my sold items/finished orders are updated as well. Are you covering such a situation in any video? I've tried to use the discount strategy but it isn't working yet :-) Thanks in advance!

Thanks so much for this useful video! I have a scenario where all of my students would be signed up to different classes. Using the junction table in the way you described would mean that every student would be included in the attendance register for each class - even the ones they were expected to attend. How do you propose we go about creating an attendance register for only classes the student is signed up for?

Thanks. I have alrady heard from the junction tables but never used. Is there a practical way to use it in Aitabel? For example if i want to „put“ students in to class or give students classes. Heve we always go to the junction table?
Thanks and God bless you!

Curious, is there any way to run this app from a trigger? Like a record gets update, or created?

This is a great video. But please explain it gradually. I feel it's a bit fast for me. Thanks.

I feel like I over use junctions tables or over populate my bases.
I'm keeping track of an assembly process. The price of the various materials vary. I have some "sub parts" which can be used in multiple bigger assembly. then I have an output schedule and a promotional schedule, a cost table, client table and a revenu table oh and a tools table 🙈 I struggle a i spend too much much of my time entering things manually.

Great video. Can you please do the same video on how to create matching automation, say like tinder for dating or better service companies with say 20 different service variations and a customer 50 different demands/needs, then once it's a match, they get notified via email and can continue communicating

the datetime format is L (small) and not 1 ;)

Hi Gareth, love your content. Would this be the best approach when creating a 'Charge Summary' document (in Documint) that displays a summary of product totals from a line-item table, this table has multiple records containing the same product names, so I don't want individual records repeated- I just want to show total hours for a given product each month. I assume a junction table would give me a list of all products per purchase order, in which I could then use roll-up fields + filtering to line items assigned that product + PO combination (excluding products with totals = 0).
TIA!

Curious about the effect of junction tables on record limits in a base. For example a full outer join between 100 records in Table A and a 100 in Table B = 10,000 permutations in the J table. The requirement to re-run the script to account for any changes in Table A or Table B means that if you remove unwanted permutations in the J table, they will get recreated each time.

This was a moment of aha😇- or do you say moment of truth. Very good explanation for what I needed to know to get better in Airtable.

Thank you Gareth!

Great explanation. Is there an easy way to create the junction table without the script? Airtable made extensions unavailable on the free plan, and I don't really want to spend $20 for exactly one extension.

Does this particular set up work if you want to try and export your database schema and converted into a real relational database like postgres or something?

Excellent video, very useful tool. But what happens if I have many students, many classes, and I don't want to create the junction table for all students against all classes? Let's suppose I have 2000 students and 60 classes, and not all students are in all classes. Is there any way to filter which students I will cross with which classes so that it only makes the appropriate combination?

do you have a video explaining how to make a employee database for a company that does event staffing services with 100s of crew that tracks assigned crew to various locations all over the US and various roles with various shifts along with certifications, performance reviews, applications filled out, etc..