sql - Implementation of blocked dates for a user-event ORM model -

in continuation of find entry value not intersect other value

i have application (ruby on rails, activerecord, postgresql) uses user table, date table , event table in order track users , events dates on these events take place. event has many dates, , user has ability sign events via different table.

the feature working on here users able block dates, can later find 1) users not have of dates in x[] blocked, , 2) side of user, events not consist of dates user has blocked.

i using primitive approach, dates stored simple strings both users , events , operators:

user.where.not("string_to_array(blocked_dates, ',') && string_to_array(?, ',')", "date1,date2...") 

i wondering if there database-oriented approaches of solving issue have better performance comparing array overlaps.

postgres version: psql (postgresql) 9.4.5

table cardinalities:

user - event: 1 many (if user of status manager)

user - event: many many through different table called invitations (if user of status client)

event - eventdate: 1 many

statements \d users

id                     | integer                     | not null default nextval('users_id_seq'::regclass) created_at             | timestamp without time zone |  updated_at             | timestamp without time zone |  email                  | character varying(255)      | not null default ''::character varying encrypted_password     | character varying(255)      | not null default ''::character varying ... , other 100 or fields first_name, last_name ... 

statements \d events

id                    | integer                     | not null default nextval('events_id_seq'::regclass) title                 | character varying(255)      |  description           | text                        |  user_id               | integer (this refers type of user, capable of creating events, not type of user referring here)                    |  project_status        | character varying(255)      | default 'create_project'::character varying created_at            | timestamp without time zone |  updated_at            | timestamp without time zone |  due_time              | timestamp without time zone |  ...  , other fields such address, referrer number... 

statements \d event_dates

id             | integer                     | not null default nextval('event_dates_id_seq'::regclass) title          | character varying(255)      |  event_id       | integer                     |  created_at     | timestamp without time zone |  updated_at     | timestamp without time zone |  date           | character varying(255) (i need explicitly store string value)     |  formatted_date | timestamp without time zone (string value converted timestamp on save) |  indexes:    "event_dates_pkey" primary key, btree (id)    "index_event_dates_on_event_id" btree (event_id) 

statements new field \d blocked_date_periods

id             | integer                     | not null default nextval('blocked_date_periods_id_seq'::regclass) user_id        | integer                     |  created_at     | timestamp without time zone |  updated_at     | timestamp without time zone |  start_date     | timestamp without time zone |  end_date       | timestamp without time zone |  

here solution chose. trick using left outer join (ruby command eager_load) users , blocked_date_periods table, , including users start_date field in joined table null, because not have blocked date objects associated themselves. query use:

user.eager_load(:blocked_date_periods).   where("blocked_date_periods.start_date null or      not tsrange(       blocked_date_periods.start_date - '00:59:59'::interval,       blocked_date_periods.end_date + '00:59:59'::interval     ) @> ?::timestamp",    date.parse(date_string)).count 

i had add , subtract 1 hour start , end date because query did not want encompass exact end dates reason, 12-26-2015 not included inside period of 12-22-2015 12-16-2015 reason yet understand.

for reason not solution , know whether there query better , faster have.