I like to use a time blocks visualization when thinking about these kinds of problems. Here's a very simple example of what I now understand to be your problem. We have three assignees in the pool of users: Alice, Bob and Carol. The problem space includes 14 periods. Let's say days but here for simplicity but you can do this with any increment makes sense for your needs. There are 5 events to assign users to: Events A - E. We are looking to schedule Event D. We can see our users availability and existing commitments in the following diagrams:
Alice's Schedule
Bob's Schedule
Carol's Schedule
In each of the above diagrams, we see the availability set for the user in green and the events they are already assigned to below that in red. At the bottom, we see the availability reduced by the events.
Now we want to find users that we can schedule for Event D. The modified availability and Event D's timing is shown below:
In the above diagram, we can see that Event D won't fit into Alice's schedule but that it will fit into Bob's and Carol's.
That's all well and good but you need this in an algorithmic approach. Once the availability periods are in this form, that is simple: find all the rows where the event fits into one of the periods.
For demonstration purposes, I'm going to lay out what I consider primitive types that make this kind of thing easier to work through in object oriented terms and then talk about how this can fit into a relational database. I'm going to use Python here. Let me know if that's a problem. There's nothing especially complex about these types and operations, they just help with the sometimes fiddly logic required.
Period
Represents a duration of time. Generally has a start and an end but you may allow either or both to be unbounded as needed. Periods need a merge (alt. combine, add, ...) operation of some sort. You also need an overlap (alt. intersection). For this problem, it's useful to have a remove method.
from typing import Set
from datetime import date
class Period:
start: date # inclusive
end: date # exclusive
def __init__(self, start: date, end: date):
if start > end:
raise Exception("invalid period")
self.start = start
self.end = end
def intersection(self, other):
if self.start > other.end or self.end < other.start:
return None
start = min(self.start, other.start)
end = min(self.end, other.end)
return Period(start, end)
def remove(self, other):
if not self.intersection(other):
yield self
else:
if self.start < other.start:
yield Period(self.start, min(self.end,other.start))
if self.end > other.end:
yield Period(max(self.start,other.end), self.end)
def add(self, other):
if self.start > other.end or self.end < other.start:
raise Exception("cannot add non-contiguous periods")
start = min(self.start, other.start)
end = max(self.end, other.end)
return Period(start, end)
def contains(self, other):
return self.start <= other.start and self.end >= other.end
def __repr__(self):
return f"({self.start} - {self.end})"
One thing to note: when working with periods, using an inclusive start and and exclusive end will greatly simplify things. If your data input or output is not in that form, I would transform it as necessary so it is. In particular, determining period adjacency is much easier in particular in this form.
Period Collection
This is a specialized collection which holds any number of periods. It's a set but the items with in it are not distinct.
class PeriodCollection:
periods: Set[Period]
def __init__(self, *periods):
self.periods = set()
for p in periods:
self.add(p)
def remove(self, hole):
# find all the intersecting periods
overlap = {p for p in self.periods if p.intersection(hole)}
# slice period out of all intersecting periods
for p in overlap:
self.periods.remove(p)
for remainder in p.remove(hole):
self.periods.add(remainder)
def add(self, period):
# find all the intersecting periods
combine = {p for p in self.periods if p.intersection(period)}
# merge all intersecting periods
# alternately: find the min start and max end
for p in combine:
period = period.add(p)
# remove all intersecting periods
self.periods = self.periods - combine
# finally insert the merged period
self.periods.add(period)
def sorted(self):
return sorted(self.periods, key=lambda p: p.start)
def __repr__(self):
return str(self.sorted())
And that's all you need for this. Here's a little script which you can run to recreate the scenarios described above:
alice_periods = PeriodCollection(
Period(date(2026,1,3), date(2026,1,15))
)
bob_periods = PeriodCollection(
Period(date(2026,1,1), date(2026,1,7)),
Period(date(2026,1,8), date(2026,1,12))
)
carol_periods = PeriodCollection(
Period(date(2026,1,1), date(2026,1,15))
)
event_a = Period(date(2026,1,3), date(2026,1,5))
event_b = Period(date(2026,1,5), date(2026,1,7))
event_c = Period(date(2026,1,6), date(2026,1,9))
event_d = Period(date(2025,1,8), date(2026,1,10))
event_e = Period(date(2026,1,13), date(2026,1,15))
alice_periods.remove(event_a)
alice_periods.remove(event_c)
bob_periods.remove(event_a)
bob_periods.remove(event_b)
carol_periods.remove(event_a)
carol_periods.remove(event_b)
carol_periods.remove(event_e)
print("alice:", alice_periods)
print("bob:", bob_periods)
print("carol:", carol_periods)
With the necessary operations defined to determine the minimal set of availability periods, we can get to the question: how do we find the people who are available for a given event?
If we recall the last figure above, we can see that all we need to do is check if the period is in one of the user's availability slots.
The sort of obvious easy solution to that is to use the code above to go through each and every user, calculate their availability slots and see if at least one of them contains the event period.
But we can do much better than that. What would be a lot better and easier to optimize would be to search all the availability periods, and use that to look up the users for those periods. That means creating some sort of map from availability periods to users. And then you'll want to sort it on start and do a search. And then what about searching on end dates... You could spend a lot of time on all of that.
Or, you could create a table in your DB like this:
| user |
start_date |
end_date |
| alice |
2026-01-05 |
2026-01-06 |
| alice |
2026-01-09 |
2026-01-15 |
| bob |
2026-01-01 |
2026-01-03 |
| bob |
2026-01-08 |
2026-01-12 |
| carol |
2026-01-01 |
2026-01-03 |
| carol |
2026-01-07 |
2026-01-13 |
| carol |
2026-01-01 |
2026-01-09 |
And load it with the availability slots you've calculated above. Looking up the available slots is just something like:
SELECT user, start_date, end_date FROM availability
WHERE start_date <= :event_start
AND end_date >= :event_end
If you put a range searchable index on start_date and end_date, I think this should perform more than adequately with the numbers of users and events you expect. The logic to create and update the availability table could be written on the database. I don't think there's an especially obvious good reason to do that, however.
I think the hard part about this problem, regardless of the solution you go with, is going to be managing concurrency. Primarily: how you make sure no conflicting assignments are created by users working at the same time. Each assignment will require removing a row from the availability and adding 0-2 back in. That needs to happen in a transaction at the very least. It's possible the availability could have been changed since the table was read. I would generally lean towards optimistic locking here (and in general) but that's getting outside the scope of the question.
