Data Model for Events (including recurring events)

[.Net2Php]

Does anyone have any suggestions on how I can represent singular/
recurring events in a database? I'm looking for a solution that would
allow me to support an interface similar to Outlook's event scheduler.
I think it may be iCal compliant.

[Warren Boyd]

I've been doing some work on using EWS to talk to exchange (not quite Outlook) - but if you have a look over the XML structure for EWS Calendar items (http://msdn.microsoft.com/en-us/library/aa564765.aspx) this may provide you some more input for what you are looking at.

Without a lot of thought into how you want to do things (i.e. you may want to provide detail to an interface that the item is recurring and how, as opposed to flagging an item in a database as recurring, but populate all the items) - I would probably tend to populate all the items in the database based upon the recurring rules and have a recurrence flag along with a unique ID to identify the "master". The advantage to that is that when you retrieve items, you don't need to calculate the rules for recurrence each time.

Cheers,
Warren.

[Jochen Daum]

Hi,

I have used the following model here: http://www.resource-scheduler.co.nz

Event: ID, start time, end time, event details, EventSeriesID

Event Schedule: ID, Event Series ID, type of repeat (weekly, monthly,
day in month), repeat details: day of week, week in month, day in
month and combinations

Event Series: start time, end time, event series details

Calendar table: day (eg from 2010-01-01 to 2020-12-31), weekday, day
in month, week in month, week in year - this is used for quick series
calculation

Time slot table: time slot (eg. 2010-01-01 00:00, 00:15, 00:30), this
is used for discretising events and calculating conflicts (time and
other resource based)

HTH

Kind Regards,

Jochen Daum

Chief Automation Officer
Automatem Ltd

Phone: 09 630 3425
Mobile: 021 567 853
Email: j...@automatem.co.nz
Skype: jochendaum
Website: www.automatem.co.nz
http://twitter.com/automatem
http://www.xing.com/go/invite/3425509.181107
http://php.meetup.com/10

P.S.: We are upgrading our phone system and there may be short outages
on our landlines. Please ring my mobile or email me, if you experience
difficulty getting through.

> --
> NZ PHP Users Group: http://groups.google.com/group/nzphpug
> To post, send email to nzp...@googlegroups.com
> To unsubscribe, send email to
> nzphpug+u...@googlegroups.com
>

[Rory Casey]

Populating all events also lets users delete/move individual events within the recurrence if they need too.  

[Jochen Daum]

Hi,

On Mon, Jan 11, 2010 at 12:13 PM, Rory Casey <ro...@rojoca.com> wrote:
> Populating all events also lets users delete/move individual events within
> the recurrence if they need too.

Yep. The main reason why we implemented it this way was that Outlook
appears to have a significant lag once you look at say 1 Jan 2011 and
have 100 recurring events in your screen. This solution has the same
speed at any given time

Jochen

[Calvin]

Hi,

Hope this can help you to get some ideas: http://martinfowler.com/apsupp/recurring.pdf

Also look at this too: http://stackoverflow.com/questions/85699/whats-the-best-way-to-model-recurring-events-in-a-calendar-application

Calvin

[Dan Khan]

Hi,

When we implemented this sort of functionality previously our schema was something like this:

* non-recurring events

* recurring events info

* recurring exceptions

We stored simple config information relating to the recurrence and any exceptions to this were flagged in the exception table.  This way we didn't have to physically store an event for every recurrence, moreso generated this at runtime using the recurrence ranges, taking into account any exceptions.  This kept the data storage requirement down to an absolute minimum important when scaled up to a couple of million users!

Cheers,

-Dan

2010/1/11 Calvin <cal...@genesisweb.co.nz>