SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Wizard holmescreek's Avatar
    Join Date
    Mar 2001
    Location
    Northwest Florida
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question A Good PHP / MySQL Calendar Schema

    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.
    intragenesis, llc professional web & graphic design

  2. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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. #3
    <?php while(!sleep()){code();} G.Schuster's Avatar
    Join Date
    Mar 2007
    Location
    Germany
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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.

    PHP Code:
    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.

    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.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    <?php while(!sleep()){code();} G.Schuster's Avatar
    Join Date
    Mar 2007
    Location
    Germany
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    and, as we all know, the simplest SELECT sql wins
    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. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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?

    Code:
    SELECT ...
      FROM events
     WHERE eventdate = '2008-09-09'
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    @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. #9
    SitePoint Wizard holmescreek's Avatar
    Join Date
    Mar 2001
    Location
    Northwest Florida
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    intragenesis, llc professional web & graphic design

  10. #10
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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 ...


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •