Trouble setting up scheduling based on date or date range

Hello good people of Airtable. I have created two tables. One for Employees and one for Jobs. The jobs are based on a date or date range.

I’ve linked the two tables and can easily select employees for each job.

The Problem: If I have a job setup for October 31, 2019 and have assigned Mike to that job, I need to eliminate Mike for any other jobs on the same date or date range. I need to remove the chance of double booking.

In pseudo code:

  1. Assign employee to Job table from Employee table (only employees available on job date or date range will be visible)
  2. If employee removed from assigned job, that employee will once again be available for job selection on previously unavailable date or date range.

The “availablity” is predicated on date or date range.

Thanks!

I would like to add on to this request. A calendar view of the jobs is straight forward. I would also like a calendar view for each employee based on the jobs assigned. The problem is the employee table has no date fields, only the link to the jobs table that has the date fields. Is there a way to overcome this challenge?