Repeat Event Calendar based - Database Design

Question

I have got similar question to: How to best handle exception to repeating calendar events.

My questions is regarding the database design.

I have some event running indefinitely however sometimes the end date is known. Lets say i am creating one event to take one medicine so i know when it is going to start but i don't know when it need to be stopped, it will depend on the progress and how the doctor advises.

Now the interesting bit is, though i will be taking medicine everyday at particular time, for some day it can CHANGE (Time, Dose etc).

So, i will have one table storing the medication name, dose, start date etc.

Questions: 1. Should there be physical record for each day [As its subject to change on particular date]. But there can be millions of record if we start to store for each date.

2. Alternatively, i can have exception for the date user is changing the details.

3. Or i can create record for 6 months and when the time will get closer either user can increase or system will increase automatically if the event is still going.

I wonder how Google calendar handles repeat indefinably.

Your suggestion will be appreciated.

Pankaj.

Answer 1

It seems to be it would be best to store the time for the next event and information about how often it should be repeated.

Example:

Next event: 2011-02-21 08:15
Repeats: Weekly (or any other format you deem appropriate)

"Next event" needs to be incremented when the time comes.

To check if the event is today you check if NOW = first part of "Next event"

Answer 2

I'd make a table with various possible conditions:

table repetition (
  event_id int foreign key,
  first_date date not null,
  last_date date not null,
  day_period int null, -- 3 = every 3 days
  day_of_month int null, -- 1..31
  -- day_of_week int null, -- 0..6, if needed
  -- whatever else
)

Now you can easily select events that happen today:

select event.*
from repetition
where
  -- (day_of_week is null or day_of_week = :current_day_of_week) and
  (day_of_month is null or day_of_month = :current_day_of_month) and
  (day_period is null or (:current_date - first_date) mod day_period = 0) and
  first_date <= :current_date and
  last_date >= :current_date
join event on event.id = event_id

An index by (first_date, last_date) will make the query plan efficient enough (an index range scan).

To make the range 'indefinite', just put last_date far to the future, like year 3000.

To handle 'single' changes in events, you can add a change_of column to event table and make it refer back to event.id. For regular events, it is null. For change events, it stores the overriding information for the event pointed to by change_of. If you fetch two events for current day, and one of these has change_of pointing to another event, you use the data from the overriding event record, and ignore the record to which change_of refers. For a one-time change, last_date = first_date.

This also allows to add recurring changes, which may be useful (regular prescription every second day and a change every 14th day), and changes on changes, which you'll probably avoid by input logic or a trigger.