Calendar Repeat Event Logic

[ryantroop]

so.. Im having trouble wrapping my noodle around this...

my initial thought:

-- events
create table Events
(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  Title VARCHAR(512) NOT NULL,
  Description TEXT NULL,
  StartDate DATETIME NOT NULL,
  EndDate DATETIME NULL
);
create index StartDate_OnEvents  On Events (StartDate);

create table EventFrequency
(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  EventID INT NOT NULL,
  Frequency INT NULL Default 0, -- 0=once, 1=weekly, 2=weekday, 4=weekend, 8=daily, 16=Monthly, 32=Yearly, 64=First Weekday Match
  OriginalDayOfWeek INT NOT NULL, --1-Sunday, 2-Monday, 4-Tuesday, 8-Wednesday, 16-Thursday, 32-Friday, 64-Saturday
  OriginalDay INT NOT NULL,
  OriginalMonth INT NOT NULL,
  OriginalYear INT NOT NULL
);

Of course, I doubt I will need original day, weekday, or year, but it's there for the sake of being there.

However, the thought is then to query EventFrequency where Month = passed in, and return all event IDs where Frequency > 0 (the original event will be handled from the first table directly). After that, further processing can be done based on the bit map and set the date as necessary (I suppose this is where original day would come in based on the frequency...)

Is this the way to go for indefinite duration? Am I missing something glaring? I am not entirely sure how to handle leap years in this case... though I would assume post processing logic server or client side can handle this.

I dunno.. any thoughts?

Thanks!

Ryan

[shophiarajan47]

IS it not possibe for you to place trigger here? Try with trigger and event to make theme usefull

[ryantroop]

I'm not sure I follow.. Are you suggesting that the trigger updates the frequency?

The idea is to limit the number of rows for less cluttered tables and faster seek times. Adding events in date slots, if that is your suggestion, seems counter productive...

http://stackoverflow.com/questions/5183630/calendar-recurring-repeating-events-best-storage-method

[ryantroop]

I had considered a similar structure to that, but I think Im gonna stick with bit maps instead. More flexibility, I feel, in terms of changing days and frequency on the fly.

Ill keep working through it... since no one else is really biting on this Im guessing this was a fairly simple question and no one is wasting time with it :-P

So.. solved it is!