Database table design for ‘availability’ within time ranges per day

I am working on a meeting scheduling web-app, where a person can share their availability for specific time ranges within the day.

E.g. A user is available 3 time-ranges within the day, from 09:10 to 10:00, 13:00 to 14:00 and 16:30 to 17:15.

This can go on for 6 days per week, from Monday to Saturday.

What I came up with:

Users:
- uuid
- sername

Days:
- pk
- name (eg. Monday)
- slug

Availability (mapping table):
- user_uuid
- day_id
- timerange

Schedule:
- pk
- schedule_id
- day_id
- user_id
- meeting_id
- time_from
- time_to
- status

But I have no clue how to query between time ranges and get all the users who are available at that time, If I search for a user who is available from 16:30 to 16:50 I am not really sure how to do that.

I’m using PostgreSQL

Availability:

user_uuid   |   day_id   |  timerange
-------------------------------------------
b1b14b30    |   3        |  '09:15 - 10:00'
a7f611ea    |   1        |  '13:05 - 14:05'
a7f611ea    |   1        |  '16:30 - 16:45'

Scheduled meeting (2 users having a meeting):

schedule_id   |   user_uuid   |   day_id   |  timerange
-------------------------------------------------------------
1             |   b1b14b30    |   3        |  '09:15 - 10:00'
2             |   a7f611ea    |   1        |  '09:15 - 10:00'

Answer

I would suggest you to first have a timerange table which is keeping the time ranges at the 5 minute interval, something like below. You should not keep time_range as textual field. Keep them in the time datatype.

TimeMaster

+----------------+------------+
| TimeIntervalId | Timevalue  |
+----------------+------------+
|              1 |    00:00   |
|              2 |    00:05   |
|              . | .          |
|              . | .          |
|            288 |  23:55     |
+----------------+------------+

Every interval representation should utilize this time master table. In this way, you can easily do all time calculations.

Users:
- uuid
- sername

Days:
- pk
- name (eg. Monday)
- slug

Availability (mapping table):
- AvailabilityId, PK
- user_uuid
- day_id
- Start_timeIntervalId
- End_timeIntervalId

Schedule:
- schedule_id, Pk
- day_id
- user_id
- meeting_id
- meetingstart_timeIntervalId
- meetingend_timeIntervalId
- status