A Good PHP / MySQL Calendar Schema

PHP
1

I’ve built a couple of calendars before using PHP and MySQL. Simple calendars that store the events in a single table - event name, date, time etc.

I’m planning a re-write of a calendar which will include repeating events.

Could someone with experience give me some tips on a good database schema?

I don’t want to get stuck doing a dozen for loops in php with a dozen db queries.

I was thinking of a varchar field to flag the repeating dates. For instance, the field would have either a 0 or 1 to represent a repeating day for any day from sunday through saturday. Thus 7 digits would represent the 7 days.

For example, 0110001 would represent that the event repeats on monday, tuseday and saturday. 1111111 would represent that the event repeats every day or 0000000 would represent that the event is not repeated.

The other throught would have a table for the basic event information and another table with entries tied back to the first table based on an event id.

I’m just kinda stuck on what fields and how to encode the data in those fields.

Any suggestions or tips would be helpful.

2

I have had something similar on the back burner for a while now, I really must get it done soon…

I am of the view that each event day should be a separate entry in the database.

In that regard, I think there are 3 distinct problems.

  1. collecting the list of possible dates (every 3rd weds of the month), and adding in default text descriptions for each. ( I cracked that )
  2. allowing the user to overide/customise each of those text descriptions, and to delete any of the dates.
  3. validate and store

How else can you cope with the CRUD-person given the annual instruction: “The chess club meets every 3rd Tuesday at 7pm in the main hall except for August when there are no meetings at all, and January to March when they meet in the Library. During the winter term they meet at 6pm.” And in late June is contacted to be told, oh, the meeting in July is at 8pm.

I just think in the real world each meeting is never an exact copy of the previous one.

If you are into OOP then take a look at Fowlers Recurring events for calendars

Good luck.

3

I think you should at least have 2 or three tables.
One fpr recurring events where you store the info as (e.g.) “every first monday of the month” and one for single entries like “18.08.2008 7pm Meeting at XYZ”.
Now you enter a recurring event in table one and when reading it from the DB you calculate if it matches the current period you’re showing.
To have exceptions you can either enter them as “negativ entries” in table 2 (like “recurringID=12, 18.08.2008 - no meeting”) or have table 3 only for this type of entries.
What you should never do is precalculate recurring events.
You can delete them as whole if you have a recurringID - but you can’t calculate them “forever” - where should you stop?
In year 2050? Bad idea if the event should only remain for 2 years.
Next year? Would mean you’d have to recalculate a year later - also a bad idea.

4

Personally I would say 1 year ahead is the max I would want to go to. If you are plotting the Formula One series for the next few years, then maybe longer, but for a social events calendar 12 months ahead should be fine.

I haven’t done more than mock some of the methods so far, but I am plumping for each date having a separate entry in the database.

e.g. imagined diary table

diary

date
title
description

So a 3 day event over a weekend would get 3 entries, one for each day. Generally a 3 day event is not the same event description repeated exactly over 3 days.


09-30-2008
Our Music Festival
Opening bands are .... 

10-01-2008
Our Music Festival
Headline bands are .... 

10-02-2008
Our Music Festival
Final day bands are ....

In my mind the major, oft-repeated select that is going to get sent to that database is the one which populates monthly calendars.

To have exceptions you can either enter them as “negative entries” in table 2 (like “recurringID=12, 18.08.2008 - no meeting”) or have table 3 only for this type of entries.

Imagining the complexity of selects banging my database to retrieve events using that kind of parameter makes me feel a bit queasy.

Then again, perhaps this is one situation in which I am guilty of premature optimisation?

I just plain don’t think its the job of the rdbms to store the complexities of repeated dates, any more than it is the job of your shopping bag to hold apples and bottles.

I feel that sorting out “how often” is merely a temporary state and is something suited to OOP/functional code in PHP, but as I say, I am all talk because I haven’t done it yet. :wink:

[edit]Oh, that and that the fact that I have made countless events diaries which adminers hate using, because adding a years worth of events is generally what they do half the time.

Getting this issue right is absolutely critical for end-users.[/edit]

5

i’m totally with cups on this

the smarts should be in the front end where the “recurring” event is first defined

all the logic should be geared toward letting the user specify the recurrence factor, with exceptions if desired, and then store each date as a separate entry

each entry can still have a “this was originally part of a recurring series” flag, but each entry will be managed as a separate entry on that date from then on

this way, the database is as simple as it gets, and you know what? so is the SELECT sql

and, as we all know, the simplest SELECT sql wins

:smiley:

6

And, as we all know, it’s idiotic to push absolutely redundant data into the database just because the developer is too lazy to do his job right.
Wanna enter a meeting for every working day for the next year? Wow, around 250 entries that could be stored in one single line.

7

g.schuster, i’m afraid you will have to try to make your point without the use of words like “idiotic”

if you have a meeting scheduled for every working day of the year, do you really need a calendar for this???

“oh wait a sec, what day is this? tuesday the 23rd? wait, let me check my calendar… no sorry, i have a meeting on that day”

once you have finished designing your “recurrence” scheme so that you can save it in one line, please enter some meaningful volumes of data, such as some daily meetings, some weekly meetings, some every other thursday meetings, some fourth wednesday of the month meetings, and some random date meetings, and then please show us your SQL to determine if a particular given date is free

i have my SQL already, can i see yours?

SELECT ...
  FROM events
 WHERE eventdate = '2008-09-09'

:slight_smile:

8

@g.schuster
I think it is easy to make the mistake of generalising, and perhaps I am guilty of that.

My POV is as a result of having produced badly thought through event diaries for local organisations.

They work, but users don’t like them because they cannot easily add repeating events. They finally figured out that if they asked me I would mass-load them into the dbase for them, then they would go back and edit each entry that deviated from the “norm”.

Now if your POV is coloured by the creation of personal booking diaries “There is a meeting of the x group every weds at 3pm, be there or be sacked.” as you suggest, then my standpoint may well not be valid and yours is.

The correct solution for holmescreek depends on the nature of the problem.

Could someone with experience give me some tips on a good database schema?

I don’t want to get stuck doing a dozen for loops in php with a dozen db queries.

Well dealing with recurring events involves managing some complexities, its up to you to decide where those complexities lie, and therefore how you are going to manage them.

9

Yeah, I managed to find some info on the iCal data file specification. Cups is right, it can get really complicated. Thanks for all of the ideas and feedback.

10

Funny you say that about iCal, I don’t think I am alone in wondering if I there is going to be a benefit in integrating with Google Calendar - either for adding or displaying events.

That’s on my todo list too.

I haven’t worked out how Google Calendar handles recurring events yet either …