1

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.

  • Is this not a duplicate of dba.stackexchange.com/a/160215/2639 ? – Evan Carroll May 1 '18 at 18:06
  • @EvanCarroll - They are similar, but in this situation, 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. – Eric Norcross May 1 '18 at 18:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.