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'
Advertisement
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