Recurring Event Question/Design

casit
casit used Ask the Experts™
on
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
--
-- 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 ;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I would do it similar way as it is implemented e.g. in Outlook. The recurring event is recorded as normal calendar event with reference to Recurrence table. The Recurrence table should contain rules for recurrence calculation of given event like RecurrenceId, Period, DaysInPeriod, SkipHolidays, MoveToNextWorkingDay, StartingDate, EndingDate, etc.

The calendar_events table should be extended by 2 columns:
RecurrenceId - FK in Recurrence table
RecurrenceBaseEvent bit - marks the calendar_events record used for base event definition (each single event generated from this base even can be updated/deleted independently on all other events)

The only question is for how long period to generate calendar events if the EndingDate is not defined.

Author

Commented:
I'm going to do yearly increments.
Can hints on the table layout for that recurrence table?

Author

Commented:
anybody?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

What do you mean by "yearly increments"? If it is calendar table records population based on Recurrence table then you don't need any change. This should be done automatically during some maintenance or when user changes the recurrence definition record or when the recurring calendar event comes.

Author

Commented:
meant I'm going to only insert events for up to a year in advance.
So I understood it correctly. If you have the recurrence end date defined then you could insert all events when the recurrence record is saved first time or updated later. If the end date is open then you may insert records up to one year and extend their number each time the event occurs.

Author

Commented:
hmm I think I was going to ask user when the event was going to end.
See my thing is that these recurring events are church meetings.
Every Sun morn @ 9am, 10am, 6pm like clock work will have an event for each site.  Now there has to be a better way to do this than inserting every single event as a row.
Any ideas?
To have every single event as a row has its own advantages:

- It is compatible with the old calendar events concept and you don't need to change it
- You may cancel or update any single occurence without affecting other events

Author

Commented:
Alrighty so next question.
What should be in this recurring table?
also how do I say get all sundays for a year in the future?
All this is already in the answer ID:24912628

Author

Commented:
Well not to be rude but obviously I'm confused with your proposal of a solution for answer 24912628.  Can you say give me a row for each table?
The real implementation should depend on your own decision. Following table structure is just a concept:

You'll create standard entry in calendar_events table and when the recurrence is required then you'll need to define record in Recurrence table:

RecurrenceId - PK
Period  char(1) - your codes to distinguish different recurrence periods 'd' = day, 'w' = week, 'm' = month, 'y' = year
PeriodOccurence int - number of periods for recurrence (this way you may define 2 weeks, 3 months etc.)
DaysInPeriod int - allows to define e.g. days in week etc. (you could define another column to allow e.g. the first monday of month)
SkipHolidays  shortint - 0 events are generated for holidays, 1 events are not generated for holidays, 2 event is moved to the nearest previous working day, 3 event is moved to the nearest following working day etc.
StartingDate - start of recurrence events
EndingDate - end of recurrence events

Recurrence period should be checked against the event length to avoid time conflicts (two days meeting with one day recurrence etc.)

Once above definition is finished you may generate calendar events for defined StartingDate and EndingDate or for the next 6-12 months. The original standard entry in calendar_events table becomes the recurrence base event definition and is marked by RecurrenceBaseEvent = 1.  RecurrenceId must be in all calendar_events related to recurrence definition.

Another option, which I don't recommend is just one recurrence definition without calendar events generation.

Author

Commented:
Thanks for the table concept.  That is awesome.  Last question.
How would I select all events that are accuring this month and also all recurring events that would be occuring this month for each day?
Let suppose recurring events are already generated in calendar_events table. To select this month events using MySQL you can simply write:

SELECT * FROM calendar_events
WHERE MONTH(start_date) = MONTH(CURDATE())
   AND YEAR(start_date) = YEAR(CURDATE())
   AND RecurrenceBaseEvent = 0

If the selected event is recurring or not will be given by RecurrenceId value existence.
If you would decide to have recurring events in separate table and not populated in calendar_events table then it would be much more complex.

If you need each day to be listed in the result then you have to generate calendar table for given period as the first step and then you have to LEFT JOIN events to this calendar. How to create calendar for given period is described e.g here: http://www.experts-exchange.com/Database/MySQL/Q_23132400.html?sfQueryTermInfo=1+calendar+gener+tabl

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial