Many to many base design

[Rick_Bosl]

I’m developing a real estate website focused on condo buildings in the Washington, DC metro area. I’m housing the data in AirTable and via Zapier, it will create/update pages in WordPress.

Two of the tables I have are for [condo] Buildings and Metro [stations]. The Metro is the subway system in Washington, DC.

There are roughly 2500 condo Buildings and 100 different Metro stations. A Building will be near zero or several Metro stations.

In the Building table, there are 3 fields that contain the names of Metro stations that are:
Field 1 - within a 1/4 mile
Field 2 - between 1/4 to 1/2 mile
Field 3 - between 1/2 to 1 mile

Each of the fields can have 0 to several Metro station names.

An example would this abbreviated record for the building Turnberry Tower:

Building Name: Turnberry Tower
Field 1: Rosslyn
Field 2: Courthouse, Pentagon, Virginia Square
Field 3: Metro Center, L’Enfant Plaza

I would like to relate each Metro station name to the corresponding record in the Metro table.

Do I need a junction table? What would that look like?

How do I create a link for each Metro name in one of the 3 fields above?

[kuovonne · Accepted solution]

Airtable is capable of handling many to many relationships without a junction table as long as you do not need any additional information about the combinations.

In your example, Field 1, Field 2, and Field 3 would all be linked record fields.

On the other hand, if you want additional information about each condo/station combination, such as the exact distance, you would need a junction table.

In either case you also need to figure out how you are going to create the links, such as a script that accesses an external api to calculate the distance between the addresses of a condo and a station.

[Rick_Bosl]

Thanks Kuovonne. I have it working now. I thought there would be more to it, but just changing the field type to linked record fields was all I needed to do.