Hi all,
I am working on a problem and have a bit difficulty about the best approach and hoping someone has some good ideas and/or have done something similar in the past. First of all, I am more interested in thoughts on "high-level" approach i.e what to do (e.g take Reservation and then get Unit and do some check etc. etc.), not the actual implementation (actual rules, processes etc.).

I have BO Reservation and BO Unit (I have multiple Units) and BO UnitStatus (1 instance per date and connected to each unit i.e each Unit has UnitStatus for all dates e.g entire 2025 etc.. UnitStatus holds the date and availability status e.g Available/Not Available) what I need to check is if there are any doublebookings i.e bookings for same unit during the same dates and the solution has to be able to handle reservations checking in/checking out on the same date i.e one reservation can check out on a date and another reservation can check in on that same date as that don´t overlap but the solution should find all other cases e.g if one reservation has check in on a date and another reservation is either staying there or checking in at the same date etc. etc, you get the idea.

I have tried to do Reservation first i.e have a process get all reservations for a period and then loop through them one by one and looking at units booked for that reservation and then looking for other reservations with the same unit during the same dates. This got messy quick and I abandoned this approach. I then did Unit first i.e take have a process to get all units and then loop though them one by one and trying to find all reservations that has this unit for these dates etc. etc.. This also got messy and I didn´t get to a good solution.

Any ideas, what is the best approach?

Would the calendar and resource model not be an easier route to take?

My ChatGPT-mate says :

You're on a great path already, and this is a very classic scheduling/booking problem with edge-case wrinkles like same-day check-out/in — so you're right to think carefully at the high level before getting lost in looping logic or complex rules.

Here’s a clean, high-level strategy to catch double bookings for units — one that scales and stays relatively tidy:

✅ High-Level Approach: Think in Terms of Date Ranges Per Unit
Instead of checking reservations or units individually, build a structure that represents each reservation as a date range per unit, and then look for overlaps.

🧠 Step-by-Step Strategy:

  1. Build a Reservation Timeline per Unit
    For each Unit, get all Reservations that are linked to it.
    For each Reservation, create a "stay window" from CheckInDate to CheckOutDate exclusive (i.e., guest checks out on the last date, so that date is available to others).
    This way, check-out and check-in on the same date won’t count as a clash.

  2. Sort Reservations by CheckInDate
    Within each Unit's reservation list, sort by CheckInDate.

  3. Iterate Through the Sorted List and Compare Overlaps
    Go through the sorted list of reservations per unit, and check each reservation against the next one in the list.
    If next.CheckInDate < current.CheckOutDate → that’s an overlap (a double booking).
    If next.CheckInDate == current.CheckOutDate → this is valid, no overlap (check-out/check-in allowed).
    This pairwise comparison avoids the O(n²) mess of comparing every reservation with every other one and gives you clear, easy-to-flag cases.

🧱 BO Structure Assumptions:

BO Reservation:
CheckInDate
CheckOutDate
Reference to BO Unit

BO Unit:
One-to-many to BO Reservation

BO UnitStatus:
You can use this for visualization or planning, but it’s not needed for detecting double bookings, since reservation date ranges carry the necessary info.

✨ Optional Enhancements:
After detecting overlaps, create a DoubleBooking BO with links to both reservations and the conflicting unit, so you can easily display/report them.
Allow exceptions (e.g., admin override or maintenance bookings) with flags in Reservation.

🧩 Why the Other Approaches Got Messy
Looping through Reservations and querying overlapping ones dynamically creates combinatorial chaos, especially when many Units or date ranges are involved.

Trying to use UnitStatus per date can feel intuitive, but it leads to massive loops or logic-heavy checks that miss the elegance of sorted date ranges.

Thanks appeciate it and I actually did just that, get help from ChatGPT (together with something I did in the past). ChatGPT (AI) is bloody awesome 🙂.