What is a Junction Table?

Linking tables correctly is arguably the most difficult element of database architecture. The most typical connection is a direct link between two tables. However, this doesn’t always get the job done adequately. In cases where this approach falls short, we have a special table called a Junction Table to fall back on.

A junction table is a special table that is created to specifically link two other tables together. This may sound redundant and unnecessary at first, but to illustrate the benefits, let’s imagine a specific scenario.

In this video, we create a list of ‘Contractors’ and a list of ‘Hourly Rates’ - these are our first two tables. Sure, we can build a direct link between the tables, but as the video demonstrates, that doesn’t always produce the optimal solution, especially when we consider the development of those rates over time. For cases (like this) where a direct link between tables doesn’t suffice, you might consider turning to a junction table.

A junction table will follow two specific rules:
It will link to 2 or more tables
The links to those tables will not permit links to multiple records per row

In brief, consider using a junction table when you can’t find a direct solution to solve your architecture. This can be a handy weapon in your architecture arsenal when you’re looking to overcome specific hurdles!

_________________________________________

Learn more at these links: 🔗 👇

Join us for our next live training!

Take our free Airtable Crash Course!

Getting Stuck? See how we can help!

Get our Airtable Field Type download!

_________________________________________

Table of Contents: 📖 👇

00:00 - What this video covers
00:58 - Framing the Example
01:15 - Trying without a Junction Table
02:05 - Why a Direct Link Fails
03:46 - Trying a Rollup?
04:34 - Introducing the Junction Table
05:09 - 2 Rules for a Junction Table
07:50 Key Takeaway

_________________________________________

Software Resources (may contain affiliate links): 💻 👇

_________________________________________

#GAPConsulting #GarethPronovost #Airtable

[ExcelTutorials]

Awesome video!!! I do mot of my stuff in excel, and am trying to build better database skills. Just curious, which platform are you making the database in?

[JustinSBarrett]

Great explanation of a junction table. Airtable aside, though, I'm curious about the odd ghosting effect that's in all of them. Whenever your hands come into frame, this ghosting appears at the top of the frame, like the whole video has been overlaid on top of itself and mirrored vertically. Do you know why that's happening?

[googlerdruw]

Airtable sure does make it super difficult to create and link cells and tables

[Mr_Tummy]

Thanks for the video, and I'm assuming, as an alternative, adding a single select field with a series of rates, to the contractor table is due to the single select does not work with currencies?

[velious]

What's your hourly rate? Don't need to waste anyone's time on a call if I can't afford it. Thanks.

[Christiaanwebb]

Could you please tell me what the exact formula to get rid of the Error message? Why doesn't just using Rates Link work? Thanks!

[pauposts]

Is there a workaround for doing UNIONS in Airtable? I want to create a combine view of multiple linked tables from different bases in my workspace. All fields are same in the linked tables.

[aroxing]

Is it all about classic N:M relation using an extra table?

[securethebag]

you should have just done this with diagrams

[XAE_A_Xii]

maonday.com or airtable? for business database and profit calculation (with many many variables, product items, customers and so on)?