Hi! This is what I do. It may be similar to what you need.
Here’s the general concept: https://support.airtable.com/hc/en-us/articles/218734758-A-beginner-s-guide-to-many-to-many-relationships#junction.
Here’s my specific application:
I need to track which people have been to which events. Each person can go to multiple events. And each event has multiple attendees. To track which events each person has been to without duplicating people, I do the following.
I have a CONTACTS table, a EVENTS table, and an EVENTS CHECK-IN table.
I use the contacts table to keep track of each person. I make sure that each person has one record and one record only. I get their info by having them fill out our “Contact Info Survey” one time.
Each Event has a single record in my EVENTS table. I manually enter the “Event Name” and “Date”.
Finally, I have an EVENTS CHECK-In table where attendees can check-in that they’ve attend an event. The EVENTS CHECK-IN table has an “Event” column that links to the EVENTS table and an “Attendee” column that links to the CONTACTS table. It also has an “Event Check-In Record” that’s a formula combining the “Event” name with the “Attendee” name.
I get this info into the table by having every attendee check-in on this form.
Now I have a unique check-in record for each attendee at each event!
##HOW IT ALL LINKS TOGETHER
Back in EVENTS, I now create an “Event Check-In Records” cell that links to my EVENT CHECK-INS table.
I also have an “Event Attendees” cell that looks up the names of every attendee to an event, and a “# of Attendees” cell that counts how many “Event Check-In Records” exist in the “Event Check-In Records” cell.
Here’s what I’ve done for # of Attendees
Back in CONTACTS I duplicated the “Event Check-In Records” column, but I also added an “Events Attended” column to display the Event name via a Lookup
When I have someone check-in to an event who hasn’t filled out the contact info form, I simply have them fill that one out first, then go back and check-in.
And that’s it! I now have completely manageable tables, with only one record per person, and one record per event with instantly updated info on who attended what via two simple forms.
Hope that helps!