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 ;
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
a little bit of sense 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.
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
hmm tooche` my man
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?
You are the database expert 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.
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?
However my problem is that this is going to be for a CMS.
