Calendar / Recursive Events

[TamedTech]

Hello Guys,

I've been working on an App for a little while which is going to require some form of scheduling of events into a database, but its one that doesn't quite meet the specification of any pre-built calendar applications available, so its been left to muggins here to start constructing somthing.

Basicly the events, as i see it, will fall into one of two catagories, they will iether be a 1 off event, with a start date and end date, or they will be a recursive event which will start on a particular date, end of a particular date but then be recursed daily, weekly or monthly.

Now i'm really struggling to conceptualize the storage method for these in the database, now the simple step would be to simple create a record in the database for every recurance of the event, when a user adds and event, the query simply loops and creates a record for every occurance for the next 12 months or somthing similar.

However, this is bound to lead to a very large and possibly unmanagable database, imagine an event that is scheduled to happen every day, you end up with 365 events all of which contain the same data, poor database design i'm sure you'll agree.

So my thoughts are that i should be placing some form of flagging system onto the records, so they contain a start date, and then another field that represents the recurance, iether it be 1, 7, 14, or whatever.

I would then need to be able to query the database for all events to occur in the next week, and then take those events and act on them with a mail or somthing.

I thought i'd ask your opinions and experience with building this kind of thing, how would you reccomed i build this section of the application?

I'm using MySQL 5 which i know has a whole stack of different date/time functions, however i'm reasonably inexperienced in using them.

Thanks guys,

Rob

 

[r937]

very large and possibly unmanagable database"?? please reconsider

very large means > 1,000,000 rows

unmanageable? shame on you!



r937.com | rudy.ca

 

[TamedTech]

Ha, ok rudy thanks for that, i'll give it some thought.

So how about this then, I set the 'begin' date for a recursive appointment, and i only ever need to access the next weeks worth of recursions at any one time.

So how about I run a script every day, which prunes the database, creating the recursive entries for the next seven days and also removing any that have already passed, as these are no longer required.

This way at worst case scenario I'll only have 7 duplicates of any one entry which isn't too bad.

So what are your thoughts on figuring out the recursion dates from the start date? is there any fancy CF or MySQL features that are likely to make life easy?

Thanks,

Rob

 

[r937]

i would recommend generating dates at least two years out in advance

don't tell me you're afraid of a table with a few thousand rows?

yes, there's an easy way to generate them, using an integers table (do a search here at tek-tips for this term)

r937.com | rudy.ca

 

[TamedTech]

Ok thanks rudy i'll give it a blast.

The only reason i suggested the 7 days terms is that is all i require, its not a standard calendar service, but i'll give it some thought.

I'll pop back later and let you know how i get on.

Thanks,

Rob