Creating a non-clashing appointments booking query…

I’m hoping someone can advise the best way of handling clashing appointments in a salon system I’m looking to build.

In its most basic form, the admin will add staff members and treatments to the system (see below tables - I’m ignoring other columns right now, just the ones I think I need).

When the customer goes to the site, they’ll choose the treatment and date. I’m thinking that the system then looks for any treatments on this date and blocks the customer from booking a treatment with the staff members involved for that time slot (i.e. 11:00 - 11:45 is greyed out).

Treatments are recorded in blocks of 15 minutes (so 15, 30, 45, 60 mins etc), there’s no 20 or 35 minute treatments.

So, the basic columns for the tables could be something like this:

Staff > id | name

Treatments > id | title | duration (in minutes)

Appointments > id | treatment_id | staff_id | start_date_time | end_date_time

So, Barbara chooses a nails treatment (15 minutes) and a date (25/05/21).

The system returns all available times for that day (i.e. as a scrolling list/dropdown) which Barbara can choose from.

At 10:30 on 25/05/21 Claire is doing a 30 minute treatment with a customer, so the 10:30 appointment slot for her is disabled so you can’t select her, however you can select Jane and Dawn who are both available at that time etc.

The problem is that I can’t quite get my head around how to check for appointments.

I was thinking of taking the start time and adding the duration (in minutes) via Carbon to get an end time. I’d then check all appointments to see which staff members are unavailable for the period the customer has selected.

Can anyone advise how I would query the appointments to do this, or if there’s a different way altogether that I’m maybe missing?

Any advice most appreciated!