Complex Scheduling Task

[JordanCopestake]

Background:

• I work for a training provider, offering courses online and in-person. We work with ~50 (and counting...) independent instructors to deliver our live Zoom and field sessions.
• When scheduling our course deliveries, our Program Managers reach out to instructors with the correct skillset and book according to availability (they're mostly self-employed so have commitments that we don't have visibility of). Once booked, PM allocates the instructor to that specific delivery. In Airtable, that's currently a link between our Instructor table and [Year] Deliveries table.
• Many of our deliveries are multi-week courses that involve instructors teaching different weeks/modules. One 10 week delivery often has 20 instructors allocated to it.
• We can have multiple deliveries running at any one time, with different instructor skillsets required.

The Problem:

• When an instructor is allocated to a delivery, they will be linked to the entire delivery and not just the dates they are teaching.
• The PMs therefore have a limited view of instructor availability. For instance, Jim might be allocated to a 10 week delivery but only actually booked to teach on week 6. Our PMs might be trying to book Jim for another delivery that's taking place in week 2 of the other delivery. From the calendar, Jim would show as booked, when he's actually available.
• Is it possible to achieve a view that shows the individual dates of each Delivery record, then enabling our PMs to allocate an instructor to specific days within the delivery Start Date/End Date parameters we already have set up?

Anything I can find so far circles back to Airtables' popular guide on creating a booking/scheduling system. This is too rudimentary for my use. I considered building something similar and treating each instructor as a "meeting room", but this would create a lot of noise in my Base as I'd need 50+ tables, one for each instructor. Thanks!

[ScottWorld]

Yes, this is possible, but you would need to completely restructure your database as a "many-to-many relationship", which is a more complicated type of relationship that requires 3 tables (instead of 2 tables) for your record linking.

You can read more about many-to-many relationships in Airtable’s support documentation here: https://support.airtable.com/docs/understanding-linked-record-relationships-in-airtable

p.s. I am also available for hire as an expert Airtable consultant to help with these sorts of projects.

[JordanCopestake]

Thanks @ScottWorld! I understand the many to many approach, but am struggling to apply this to our need to allocate an instructor different parts of a delivery. 

I have 3x tables in my base; one for our list of products (courses), one for our list of instructors, and one that - in theory - serves as the junction table between the others. This third table lists all booked deliveries and links the appropriate product and instructor(s), whilst including specific start/end dates (and location etc) for that delivery. 

How do you envisage the many-to-many looking in order for it to allow me to allocate an instructor to part of a delivery, rather than the whole thing (e.g. book them for 15th and 16th Jan for a delivery that runs 1st to 30th)?

[ScottWorld · Accepted solution]

Oh, I see... you're already doing a many-to-many relationship.

I believe that in order for it to work, you would need to break down your junction table even further than it is already broken down. You would need to break it down to the smallest possible segment... which would be a day. So your junction table would need to have one record for each day.

You could possibly do that in a 4th table. You could possibly write a script that loops from the start date through the end date in the 3rd table, and creates a new record for each day in the 4th table. Then, you could assign the instructors day-by-day in the 4th table.

But if you could break it all down in the 3rd table by day, that would be ideal because there are less tables to deal with.

[JordanCopestake]

Yeah, I'm thinking a table holding records for each day of the year, with cells linked to instructor and deliveries tables will be the best way forward on this one. Potential to get a little messy when we have deliveries running in parallel but can use colour formatting etc to help with clarity there. 

Thanks for your input on this, @ScottWorld 🤜