Modeling availability and time slots in MongoDB for auto-assigning tradesmen

[sirajahmedx]

How do you model availability and time slots in MongoDB to efficiently support auto-assignment of tradesmen?

[ilhanklisura]

Hi,

I’d usually keep it simple with three collections:

• tradesmen (skills, timezone, capacity per slot)
• availability (date, ranges of working hours, plus exceptions)
• bookings (start/end times, job info)

When auto-assigning, you:

1. Filter tradesmen by skill.
2. Check availability for that date.
3. Subtract existing bookings and exceptions from the ranges.
4. Pick a free slot where bookedCount < capacity.

If you need speed, you can pre-generate discrete time slots (e.g. 15-min blocks) and just findOneAndUpdate with a condition on bookedCount, which makes the assignment atomic.

Everything stored in UTC, and convert back on display.

[sirajahmedx]

Yes, I also did some research, and I was planning to go with a similar approach. The third collection I have is timeslot, where I store the startTime and endTime of the booking. In addition, I save the assigned tradesman in the timeslot so that during auto-assignment, I can first check if they’re available at that time. If not, I will then look for other users who provide the same service. Is this good to go search for the tradesman first and then check in the timeslot collection?

[tejas764]

Two main ways to model availability in MongoDB:

Slot-based model (pre-generated time slots like 09:00–10:00)

• Easy to query (status: available)
• Fast auto-assignment
• Uses more storage
• Range-based model (intervals with start and end times)
• Flexible for variable durations
• More space-efficient
• Requires overlap checking logic

Efficiency

Use indexes on trade, availability.start, availability.end (and location if needed).

For large scale, shard by location or trade type.

Auto-assignment flow

Filter by trade + location

Check availability (slot or range)

Exclude overlapping bookings

Apply tie-breakers (nearest, least busy, earliest slot)

[sirajahmedx]

Thanks for the breakdown. I’m using a range-based model with a timeslot collection where I store startTime, endTime, and the assigned tradesman. During auto-assignment, I first check the tradesman’s availability in that time slot. If they’re not available, I look for another user providing the same service. Is this approach good to go?