Storing Calendar Events / One-time vs recurring

[Jessica]

I'm designing a system that will have events, both one-time and recurring. Trying to decide on my table structure - at this point I'm thinking two separate tables for the event details (with one parent table holding the basic similar data). 

 

I'm having trouble with finalizing how I want to store everything for the recurring table. The types of appointments would be things like:

 

Every day at noon from START_DATE to END_DATE

3 times per day every day from START_DATE to END_DATE (maybe store these as three seperate events?) - it's very likely a recurring event would often be 2-3 times per day.

mon, wed, fri every week, no end

a set day every month

a set day every year

 

So the fields I'm thinking so far are:

 

start_date DATE (optional)

end_date DATE (optional)

start_time TIME (required) (all events require a time, but if it's a recurring event without an end date it won't have dates)

end_time TIME (required)

repeats_on (optional varchar, possible values like MM-DD)

repeats_every (optional varchar: possible values like "mon,wed,fri" or "daily" or "weekly")

 

 

Any input? I'm off to do some searching for more ideas.

 

 

 

 

OR: Do I use PHP to generate the events for each of the ones with start and end dates, and store them in the regular table (and somehow link them together), and then also generate them for up to a certain amount of time for the others?

[ignace]

Here is a crazy idea, how about using the same working as a cronjob? The problem is that a DATETIME type has no option to blank out certain value (like month, day, or year) which is what you actually need. And using words like 'recurs' or something like it only makes it harder to query.

 

The database schema would be something like:

events (id, date_start, date_end, ..)
events_dates (event_id, date_id)
dates (id, minute, hour, day, weekday, month, year)

Assuming NULL means * you can construct your queries to find the events that occur today, this week, this month, this year. Things like */3 would have to be resolved to multiple dates. Though I guess https://github.com/mtdowling/cron-expression could help achieve you get those dates.

 

The event would have another field that defines when the event ends. The date start will help you query for any relevant events, date_start is always set (creation date otherwise). When date end is NULL then it is ongoing. The dates table holds how many times it should occure.

 

What do you think?

Edited April 27, 2013 by ignace

[Jessica]

I like it - that never occurred to me but it makes it a lot clearer. I see a similar thing suggested on stack overflow.

 

I think that would work nicely, especially by making the events be able to have more than one entry on the date/Cron table, I can have a lot more flexibility on the rules too.

[Jessica]

I guess I'd also have to add in how long the event lasts, rather than using an end time I think just storing the length is better.

[Barand]

If you have a requirement that events should not overlap then it's easier if you store start/end instead of start/duration