"Selective" junction tables?

[figuringthingso]

Hi! I’m new to both Airtable and building databases more generally. I have a question about junction tables.

I’m trying to build a table similar to what is described in this video and on this page. But I’m stuck on a specific thing. I’m trying to build a junction table that contains a record for each association between one of 15 training programs and one of 51 faculty members. The GAP video and the Airtable support page suggest that such a table would have 615 results - one for each intersection between each of the 15 training programs and each of the 51 faculty members. And indeed, when I build my junction table, I get many hundreds of records.

But the thing is, only about 10 or so of the 51 faculty members taught at any given one of the programs. The only way I’ve managed to build a junction table gives me a record for the intersection of each faculty and each program–even ‘false’ intersections between faculty and programs they didn’t teach at. The table that captures the data I really want would only have about 150 records. So how do I build a junction table that omits associations between faculty and the programs the faculty members didn’t teach at?

Thanks!

[ScottWorld · Accepted solution]

Welcome to the community, @figuringthingsout!

Your junction table is only required to have data for the intersections that ACTUALLY exist, not the intersections that COULD exist.

So, for example, if you only have 15 faculty members who have each taught 1-2 programs, that would be 15-30 records in your junction table.

(Also, keep in mind that not every database system needs a junction table at all. You only need a junction table if you need to keep detailed information about each particular intersection.)

[kuovonne]

If you’ve already created these extraneous junction records, just delete them. If they do not exist yet, don’t create them.

Junction tables are more about the structure of the base tables and fields, not so much about having a record for every combination.

[figuringthingso]

Thanks @ScottWorld and @kuovonne!

So it occurs to me that we’re talking about two different things, at least at this level of the game. There are junction tables that are created automatically using some kind of script or app or automation (like Airtable’s Junction Assist script), and junction tables that are created by hand. Am I right that when you say that they’re only required to have data for intersections that actually exist, you’re talking at least about the latter (junction tables created by hand)? Because, at the very least, the Junction Assist script seems to create all possible intersections.

[kuovonne]

Creating junction records by hand can be a very tedious, time consuming task. So the author of the script decided to write a script that makes that task easier. The script has no way of knowing if some combinations do not make sense, so it just makes all of them. This is neither good nor bad. This is just what the script does. It is up to you to decided if you want the functionality that the script provides.

If you’ve already run the script, just delete the records that you do not need. If you have not run the script, decided if it will be easier to manually create the records by hand, or to run the script and delete the extras.

[figuringthingso]

Thanks @kuovonne, this all makes sense.

kuovonne:

decided if it will be easier to manually create the records by hand, or to run the script and delete the extras

Decidedly easier to do it by hand. I’ve got 16 events and ~500 people, and most of the data is historical. So I’d much rather create a couple hundred records in batches of a dozen or so at a time than delete 7800 records before I can start using the table for upcoming events.

Thanks much!