Skip to main content
Solved

"Selective" junction tables?

F

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!

Best answer by ScottWorld

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.)

View original
    Did this topic help you find an answer to your question?

    5 replies

    ScottWorld
    Forum|alt.badge.img+33
    • ScottWorld
    • Brainy
    • 8831 replies
    • Answer
    • November 3, 2021

    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.)

    F
    kuovonne
    Justin_Barrett
    3 people like this
    • F
      figuringthingso
    • kuovonne
      kuovonne
    • Justin_Barrett
      Justin_Barrett
    kuovonne
    Forum|alt.badge.img+27
    • kuovonne
    • Brainy
    • 6006 replies
    • November 3, 2021

    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.

    ScottWorld
    F
    Justin_Barrett
    4 people like this
    • ScottWorld
      ScottWorld
    • F
      figuringthingso
    • Justin_Barrett
      Justin_Barrett
    • Kamille_Parks11
      Kamille_Parks11
    F
    ScottWorld wrote:

    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.)


    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
    Forum|alt.badge.img+27
    • kuovonne
    • Brainy
    • 6006 replies
    • November 4, 2021
    figuringthingso wrote:

    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.


    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.

    F
    1 person likes this
    • F
      figuringthingso
    F
    kuovonne wrote:

    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.


    Thanks @kuovonne, this all makes sense.

    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!

    kuovonne
    1 person likes this
    • kuovonne
      kuovonne

    Reply


    Most helpful members this week

    Terms & Conditions