Recurrence pattern - events schedule

[hannibal]

Hi,

 

I have searched the forum & Google to find out where to start with a recurring event calendar, but not having much luck, although there are similar posts with similar request. I do not want to go down the route of creating the recurring events in a separate table.

 

If I created an event on 01/01/1970, and it recurred every Tuesday, how would I query MySQL to show all events for a given day? So, if I looked at next Tuesday, all events for the Tuesday would be listed.

 

SELECT * FROM events_table WHERE event_date='1970-02-16'

- This would not show me my event as I do not have this date stored. My start date is 01/01/1970 for example.

 

SELECT * FROM events_table WHERE event_date="Recurs on a Tuesday/or any recurrence pattern (monthly, etc)"

- This is the "sort" of thing I am after.

 

It is so difficult to try and create a stable and efficient way of doing this, I don't know where to start at the mo. Any links to recurrence SQL calls much appreciated.

 

Thanks,

Hannibal.

[revraz]

01/01/1970 is a horrible date format to store dates in.  Use something that works like Unix Timestamps or MySQL datetime stamp.

 

Unless I am misunderstanding your example.

[hannibal]

Revraz,

Thanks for your quick reply. This was litterally an example date, it is more of the recurrence feature that I am seeking. The date will more likely stored as YYYY-MM-DD.

Thanks,

H.

[obsidian]

There are really only two viable approaches to storing recurring events (I've used both, and which you use really depends on how much flexibility you want):

 

1) At the time of entry, calculate the recurrence to a set future date and insert a record for every one. You can easily add a reference column that stores the ID of the key (or first) entered event to keep them all tied together as a single entry. The speed of querying is much faster with this, but to edit or update the rules of the recurrence are nigh impossible since you are not actually storing the rule, only the occurrences of the event.

 

2) Have a second table that simply stores the algorithm for determining when the event will take place (ie, monthly on the 2nd Thursday or every other Tuesday). Then, your key event has a flag that simply marks it as recurring. This takes a bit longer on the calendar load, because you will need to sort every recurring event to see if the algorithm falls on the current date, but your flexibility is limitless because you have not only the event details stored but the actual definition of the recurrence as well.

 

Hope this helps some.

[hannibal]

Thanks obsidian,

 

It is the second option I am after. However, creating this type of algorith is very difficult and I do not know where to begin. I was hoping someone could point me in the right direction or has done something like this before.

 

I can image it is going to be on hell-of-a complicated SQL call, with all sorts of conditions and dateAdd(), etc.

 

Outlook does this task very well, and I am sure MS do not create a record for each occurance. Must be a way, just how!?!

 

Thanks,

H.

[Barand]

There are really only two viable approaches to storing recurring events (I've used both, and which you use really depends on how much flexibility you want):

 

1) At the time of entry, calculate the recurrence to a set future date and insert a record for every one. You can easily add a reference column that stores the ID of the key (or first) entered event to keep them all tied together as a single entry. The speed of querying is much faster with this, but to edit or update the rules of the recurrence are nigh impossible since you are not actually storing the rule, only the occurrences of the event.

 

2) Have a second table that simply stores the algorithm for determining when the event will take place (ie, monthly on the 2nd Thursday or every other Tuesday). Then, your key event has a flag that simply marks it as recurring. This takes a bit longer on the calendar load, because you will need to sort every recurring event to see if the algorithm falls on the current date, but your flexibility is limitless because you have not only the event details stored but the actual definition of the recurrence as well.

 

Hope this helps some.

 

There is a hybrid solution giving the advantages of both. At time of entry create the occurences and also store a "rules" record. (The events would contain the rules id). If the rule changes, delete future events for that rule and generate new

[hannibal]

Thanks Barand, but I really wanted to get away from any record creation of the occurences. I may have to do it a long winded way and limit what sort of recurrences the user can have. I just thought that someone would have had experienced a similar issue to mine.

 

It must be able to be done, but it is obviously quite complicated with a very nice algorithm to calc days which the event can be displayed on.

 

Cheers,

H.

[Barand]

Outlook does this task very well, and I am sure MS do not create a record for each occurance.

 

 

I'm pretty sure they do. You get the option to edit just the single occurence or the whole sequence, which wouldn't be possible unless the single occurence existed,

[hannibal]

Correct, you can edit the series "or" this instance. If they were to edit "this instance", then surely Outlook could "create" the instance at this time.

 

Process:

-Double click to open "future non-existant" event (i.e. one that outlook has programmatically placed.

-User clicks "Edit this instance"

-Details are loaded from initially created event (such as detail and start/end time).

-When saved, this event is then physically created as a record.

 

It appears seamless to the user, but for Outlook there are now only two physical records. The original record, which has the recurrance pattern, and the "new" record which we have just updated (created behind the scenes). This new record is no longer linked to the recurrence pattern - as it has been changed.

 

Do you follow my method of thinking? 

[Barand]

This new record is no longer linked to the recurrence pattern - as it has been changed.

 

Do you follow my method of thinking? 

 

I follow it, but I think the "new" record is still linked to the pattern, as it was originally

[hannibal]

Sorry, yes it is still linked (as in related), but does not move when you move the other appointments. (as you may have changed the time to start an hour later on this individual appointment for example).

 

I think the short story is, I am going to have to come up with a simple algorithm with a complex SQL query to find my recurrences as I don't think creating x records is the solution to my issue - although it may suit other peoples solutions.

 

Thanks for your time on this issue.

 

In the meantime, if anyone has any further comments, please post.

 

Thanks again Barand.

H.

[obsidian]

I tend to agree with Barand on this one, since with Outlook (since this is the example cited) you are able to edit individual instances of a recurring event without disrupting the pattern. I believe that this might be the best overall solution for load time, but again, what you wish to do is completely your choice. Rather than get knee deep in the discussion of Outlook, however, I'll try to give you a little more detail regarding the algorithm and storage that I have used in the past.

 

I came up with tables that looked something like this:

CREATE TABLE recurring_rules (
  rule_id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY, -- PK, obviously
  event_key INTEGER UNSIGNED REFERENCES events (event_id), -- FK to the main entry event
  rule_type ENUM('daily', 'weekly', 'monthly'),
  op char(1) DEFAULT NULL,
  op_offset TINYINT UNSIGNED NOT NULL DEFAULT 0,
  days TINYTEXT DEFAULT NULL,
  negative TINYINT UNSIGNED DEFAULT 0
);

 

I might be missing some of the other nuances of the different algorithms in there, but this will at least suffice to show you one way of handling things. Rather than trying to explain them all in detail, I'll give you a few records and explain what they do. In addition, in my table above, the "days" column stores the numeric representation of the days (either for monthly or weekly recurrences) as a serialized array. I'm sure there are better ways (more normalized with another table for instance), but this is just the method I have chosen in this case.

 

Record #1 - Every other day

Record #2 - Every third Tuesday and Thursday

Record #3 - 15th and 30th of every month

|----------------------------------------------------------------------------|
| rule_id | event_key | rule_type | op | op_offset |     days     | negative |
|----------------------------------------------------------------------------|
| 1       | 1         | daily     | %  | 2         |              | 0        |
| 2       | 23        | weekly    | %  | 3         | array(2,4)   | 0        |
| 3       | 140       | monthly   |    |           | array(15,30) | 0        |
|----------------------------------------------------------------------------|

 

Keep in mind as well, that the negative column is in case you want to allow for someone to do something like every day except my pattern.

 

Hope this helps some.

[hannibal]

Thank you odisian... That is certainly a large step forward. I am sure from the information you have provided will give me enough of a headstart to produce my dynamic SQL Query to show what events are on a certain day, without having the physical record. Something like:

 

Select * from Events Where start_date='2008-07-31' UNION Select *.Events FROM recurring_rules LEFT JOIN Events ON recurring_rules.event_key=Events.event_key WHERE SQLRecurranceConditionMatchesDate.

 

First parts gets appointments which do not have a recurrance for today, and the second call gets the recurring events.

 

Thanks again,

 

H.

[riti]

Hi,

 

i am also working for same recurrence pattern for scheduling time slots.

Can you please provide Database structure for scheduling slots(events) and recurrence pattern ASAP.

Also post mysql/sql query if you have used any for fetching these slots and recurrences.

 

Thanks.

[obsidian]

Hi,

 

i am also working for same recurrence pattern for scheduling time slots.

Can you please provide Database structure for scheduling slots(events) and recurrence pattern ASAP.

Also post mysql/sql query if you have used any for fetching these slots and recurrences.

 

Thanks.

 

We're not in the business of writing systems for people entirely: that is what the freelancing section is for. If you have specific questions regarding the direction to head or clarification on some code with which you are struggling, feel free to request assistance, but this is not the section to inquire about someone providing your full schema and/or code for you.

[bminer]

Hi,

 

i am also working for same recurrence pattern for scheduling time slots.

Can you please provide Database structure for scheduling slots(events) and recurrence pattern ASAP.

Also post mysql/sql query if you have used any for fetching these slots and recurrences.

 

Thanks.

 

I am writing an article on how to do this... check it out!

 

http://groups.google.com/group/blake-miner/web/storing-recurring-events-in-mysql