CODEWITHSUNDEEP
sqldatabasepostgresqldatabase-design
ekclone
ekclone

Reputation: 1092

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'

Upvotes: 0

Views: 887

Answers (3)

Venkataraman R
Venkataraman R

Reputation: 12969

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

Upvotes: 1

detzu
detzu

Reputation: 756

The Availability table should be defined like this :

Availability (mapping table):

  • user_uuid
  • day_id
  • timerange_start type TIME with time zone
  • timerange_stop type TIME with time zone

Then to find all users that are available that day is to run

select * from Availability where day_id = YOUR_DAY_ID and 
timerange_start < YOUR_TIME_START and 
timerange_stop > YOUR_TIME_STOP

I think one week is not enough , so you should save the full date. You need to mark that a person is assigned to a meeting , so either to delete the row from Availability or to add a status field. Good luck !

Upvotes: 1

Shantanu Kher
Shantanu Kher

Reputation: 1054

Using range types is certainly a good solution for your use case. However you can think of storing timestamps (range_start and range_end) in epoch format (mainly bigInt datatype). Methods to convert epoch to readable timestamp and vice versa are available with PostgreSQL.

Epoch values will keep the implementation, primarily around availability check, simple.

https://www.epochconverter.com/

Upvotes: 1

Related Questions