What's the best way to create a booking calendar system?

[imgrooot]

Here's what I'm trying to do.

1. A user creates a calendar that shows which dates he's available. This calendar could showcase availability for up to a year.

2. Other users can do a search for a user that's available on a set date. For e.g. July 1st. 

3. All the users available on July 1st will show up in the search results.

 

There is no booking for appointments involved. It simply needs to show the users available on set dates. I am wondering what's the best way to create this calendar feature?

[maxxd]

Available at all on a select date or available within a given time frame on a select date? If the only spec is that there are no other appointments or events on that person's calendar on that specific date, that's a simple question of a subquery where select all events on date are null. If you're talking time, that gets complicated with start times, end times, and overlaps of either or both.

Edited June 25, 2021 by maxxd

[requinix]

If you only care about checking individual dates that are available, that's fairly easy: store the unavailability dates as rows of start and end dates, then search for rows based on whether the day is between them.

If you care about checking date ranges then it's a bit trickier, but still not as complicated as it might sound: a conflict is any row where the desired start date is before the unavailable end date, and the desired end date is after the unavailable start date.