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
