TL;DR - Is my approach of using separate date
and time
columns correct given that the availability of a user is set on a day-of-week basis and is independent of the calendar date.
I'm using PostgreSQL 9.5
and working on a booking / availability project where a user can set their daily availability for a given date range; for instance:
User: 1
Range: 04/30/2018 - Infinity
SUN: 12:30:00 - 16:00:00
MON: 08:00:00 - 23:59:59
TUE: 00:00:00 - 23:59:59
WED: 00:00:00 - 16:30:00
THU: 08:00:00 - 23:59:59
FRI: 12:00:00 - 23:59:59
SAT: 12:00:00 - 23:59:59
In order to accomplish this, I was considering setting up the following tables, and I would like feedback/constructive criticism on my approach:
users
Column | Type | Modifiers
---------------+-----------------------------+----------------------------------------------------
id | bigint | not null default nextval('users_id_seq'::regclass)
email | character varying | not null default ''::character varying
created_at | timestamp without time zone | not null
updated_at | timestamp without time zone | not null
first_name | character varying(255) |
last_name | character varying(255) |
date_range
Column | Type | Modifiers
------------+-----------------------------+-----------------------------------------------------
id | bigint | not null default nextval('ranges_id_seq'::regclass)
created_at | timestamp without time zone | not null
updated_at | timestamp without time zone | not null
start_date | date | not null
end_date | date |
user_id | bigint | not null
time_range
Column | Type | Modifiers
---------------+-----------------------------+---------------------------------------------------------
id | bigint | not null default nextval('time_slots_id_seq'::regclass)
created_at | timestamp without time zone | not null
updated_at | timestamp without time zone | not null
date_range_id | bigint | not null
start_time | time | not null
end_time | time | not null
weekday_id | integer | not null
The goal of which would be to return users
who have availability within a given date, and time range.
Start Date: Monday, April 30th
Start Time: 10:00 AM
End Date: Wednesday, May 2nd
End Time: 2:45 PM
For instance, given the above input and initial dataset, I would want to perform a search which would return a user
record with an id = 1
, because they are available within the full 52.75 hr range of the input parameters.
* I've neglected to include Time Zone related considerations for the sake of brevity.
I'm also considering using a daterange
type for the start_date
and end_date
. And had considered using a tsrange
for the start_time
and end_time
; however, tsrange
includes the date, and I didn't think that would be the best option considering that time_ranges
are date-independent – That being said, I'm open to anything which makes this more efficient / easier to navigate.
Additionally, I'm guessing I'll need to use relational division
when building the query?
EDIT I've found some good tutorials on similar setups, but they have all dealt with bookings where the event has a set start and end date and time. My understanding of what I'm trying to accomplish (and perhaps that understanding is incorrect) is that, a user needs to set general availability on a day-of-week basis as opposed to specific calendar dates, in order to avoid having a ton of repeating ranges.
I'm totally open to other setups or even just links to resources which can help me gain a better understanding of how to approach this problem.
time_ranges
are date-independent, where as in that example, a reservation is make for an event that has a specified date and time. That's not to say that my approach is correct though, which is why I was looking for feedback as my SQL-fu is far from proficient.