I have a base which relates artists and exhibitions. When I want to see all the exhibitions (over 50) in which an artist participated, I want to see them in chronological order. In the same way, when I want to see all the artists (more than 50) who participated in an exhibition, I want to see them in alphabetical order. For the time being, I can only see them in the order in which I entered the various records. Where can I learn more about this?
I am precisely at the stage where I am trying to decide the best way to design this database. Can you direct me to some information which would allow me to weigh the pros and the cons of adding a junction table or linking the two tables directly. I have a lot of data that I will eventually want to import from another source. This is an academic research project. I am willing to devote some time to getting a firm grasp of the fundamentals of Airtable.
Kuovonne & Scottworld have both pointed you in the right direction.
Anytime you have a cardinality of many-to-many it is recommended to use a junction table. However this choice will be up to you. I use a junction table to join my tables when I can.
In your case, 3 tables: Artist, < Artist_Exibition, > Exhibition. Artist contains artist attributes, the Exhibition contains exhibtion attributes, the Artist_Exibition table will contain a combination of the Artist and Exhibition tables plus any new relations you want to add, this is where you would do all your views, filtering, grouping, etc.
Airtable is different from most other relational databases in that it does not require a junction table for a many-to-many relationship. If you don’t need a junction table, you can make data entry a little easier by not having one.
However, there are many good reasons why your data and/or reporting could benefit from a junction table. To expand on @Pablo_Rios’s comments, ask yourself some questions:
Do you want to keep track of anything unique to a specific artist/exhibition combination, such as
the specific piece of artwork that an artist had at that exhibition
the number of pieces of artwork the artist had at that exhibition
any awards the artist received at that exhibition
Do you want to be able to group, filter, and sort exhibitions and artist in the user interface?
If you answered “yes” to any of the above questions, you should create a junction table.