Recurring Events


#1

Hello,
I have an existing calendar/events table that I use to keep track of single and multi-day events. However I need to be able to also have recurring events also.
Now we don't have to add recurring events to this table. I could select out of both tables to grab my calendar/events data. I'm just really confused on the best way to setup db/coding for recurring events.
Here is my current table structure for events

Or should I just insert a new record into the events table for each recurring event? seems like a bunch of work though on editing, adding, and deleting recurring events.

--
-- Table structure for table `calendar_events`
--

CREATE TABLE IF NOT EXISTS `calendar_events` (
  `event_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) DEFAULT NULL,
  `creator_id` int(11) DEFAULT NULL,
  `created` date DEFAULT NULL,
  `last_modified_id` int(11) DEFAULT NULL,
  `last_modified` date DEFAULT NULL,
  `title` varchar(100) DEFAULT NULL,
  `visible` smallint(6) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  `content` text NOT NULL,
  `location` varchar(255) NOT NULL,
  `ministry_id` int(11) NOT NULL,
  PRIMARY KEY (`event_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;


#2

adding individual instances of a recurring event seems like the simplest approach

note that every recurring event is eventually going to run into that one day where it actually doesn't recur, like the regular monthly meeting that always happens on a thursday except of course when it falls on december 25th, so the scheme that you implement for recurring events has to be able to handle when it ~doesn't~ recur

if you generate the recurring events into specific instances, then of course it is easy to handle the non-recurring instances -- these just don't get added to your main table

you do need a second table for the recurring events, but you only need it to generate the specific instances in advance for however far in advance your schedule goes, e.g. once a year you use the recurring table to generate the next year's specific instances

make sense?


#3

a little bit of sense smile However my problem is that this is going to be for a CMS.
which will hopefully have a couple hundred sites on it. And each site is going to have at least 4 recurring events every week. so thats 4 rows per week times 52 weeks a year times say 100 sites.
Thats alot of rows just for 4 events weekly for one year.

Any other ideas?


#4

what's wrong with a lot of rows?

this isn't the 20th century anymore, back when computers came with 2 gigs of hard drive that cost $400

nowadays it's more like 400 gigs for $2

okay, that may be an exaggeration, but i mean really, what's wrong with a lot of rows if that's what it takes to store your data in a simple fashion?

sure, you could store a recurring event in one single row that says "here is the rule for this recurring event" but then you have to "reconstruct" the recurrence on the fly every time you want to access the calendar

remember, computation on the fly is a lot more server-intensive than a simple row retrieval

simplicity rulez!!!


#5

hmm tooche` my man smile
I will stick with the single row approach(also far easier for me to implment).
would you recomend any specific table layout for the recurrances?


#6

you'll stick with the single row approach? the one i just tried to talk you out of?


#7

You are the database expert smile I have followed you on the sitepoint forums for like 4-5 years now. I figure you have seen just about every type of question and application imaginable in a database. so yeah what you recommend with I will go with.


#8

okay so you will store the definition of a recurring event in a recurring events table, but will use this only to generate actual event instances into the main table, right? and the main table is the only one you access at run time when displaying the calendar, right?

whew! you had me worried there for a sec

cool


#9

ok so after reading your last post I'm just a little confused. Can you jot down some example table layout for recurrance definitions?


#10

i'd be happy to -- could you start me off with some sample data of a recurring event?


#11

cmuench: You may have a look at this older post:
http://www.sitepoint.com/forums/showthread.php?t=477802
I still think that my design is good idea wink


#12

i still think so too, mastodont

especially this part --

that sounds exactly like what i've been saying in this thread

smile


#13

Yes I agree.
@Mastodont can you post those create table scripts?
@r937 thanks for all your help.


#14