CODEWITHSUNDEEP
sqldatabasepostgresql
Raj Sekar
Raj Sekar

Reputation: 41

calendar database query with repeat interval function in postgres

I have three tables for calendar function. Say event table has event_id and description etc.

event_schedule table has event_id and event_start_date and event_end_date.

event_repeat table has event_Schedule_id and event_start_date and

repeats(it can be weekly,monthly,daily,yearly) and repeat_every(1,2,3 this is interval)

repeat_on(mon,tue,wed,etc.sometimes blank) and starts_on(starting date its same date which is in event_schedule table) and ends_never(keeps going on may) end_occurences(no_of occurence), ends_on is end date, event_schedule_id mapped to event schedule table and repeated_by_month is (if monthly this will help to decide its start of the month are end of the week) please find the table structure

CREATE TABLE events
(
  event_id serial NOT NULL,
  event_name text,
  city text,
  description text,
  active_status text,
  discount text,
  user_id integer,
  inserted_date timestamp without time zone,
  modified_date timestamp without time zone,
  state text,
  CONSTRAINT event_pkey PRIMARY KEY (event_id ),
  CONSTRAINT event_user_id_fkey FOREIGN KEY (user_id)
      REFERENCES users (user_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)

CREATE TABLE event_schedules
(
  schedule_id serial NOT NULL,
  schedule_mode text,
  start_date date,
  end_date date,
  start_time time without time zone,
  end_time time without time zone,
  repeat text,
  activity_id integer,
  business_hours text,
  modified_date timestamp without time zone,
  CONSTRAINT event_schedules_pkey PRIMARY KEY (schedule_id ),
  CONSTRAINT event_schedules_event_id_fkey FOREIGN KEY (event_id)
      REFERENCES events (event_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)


CREATE TABLE event_repeats
(
  repeat_id serial NOT NULL,
  repeats text,
  repeat_every text,
  repeat_on text,
  starts_on date,
  ends_never boolean,
  end_occurences text,
  ends_on date,
  event_schedule_id integer,
  modified_date timestamp without time zone,
  repeated_by_month boolean,
  repeat_week text,
  CONSTRAINT event_repeats_pkey PRIMARY KEY (repeat_id ),
  CONSTRAINT event_repeats_event_schedule_id_fkey FOREIGN KEY (event_schedule_id)
      REFERENCES event_schedules (schedule_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)

Upvotes: 0

Views: 776

Answers (1)

Chris Travers
Chris Travers

Reputation: 26464

I think you are going to have to use the "repeat" schedules merely for initial scheduling. Things might get rescheduled so you don't want to rely on it as authoritative. Periodically (maybe once a month?) you may want to go through and use that logic to create new events. To make that work you'd have to add a "most_recent_repeat" field or the like to your scheduling table and that could either be a date or a self-join. At any rate, each event should have its own scheduling entry for each occurrence.

If that doesn't answer your question it isn't clear what you are asking. Perhaps you could clarify?

Upvotes: 0

Related Questions