Jump to content


Photo

Storing Calendar Events / One-time vs recurring


  • Please log in to reply
4 replies to this topic

#1 Jessica

Jessica

    This is not my name.

  • Gurus
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 26 April 2013 - 07:47 PM

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?


My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!

#2 ignace

ignace

    Now mod flavored

  • Moderators
  • 6,208 posts
  • LocationBelgium

Posted 27 April 2013 - 04:06 AM

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/m...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 by ignace, 27 April 2013 - 04:47 AM.


#3 Jessica

Jessica

    This is not my name.

  • Gurus
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 27 April 2013 - 11:26 AM

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.
My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!

#4 Jessica

Jessica

    This is not my name.

  • Gurus
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 27 April 2013 - 11:28 AM

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.
My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!

#5 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 13,881 posts
  • LocationCheshire, UK

Posted 30 April 2013 - 03:36 PM

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


|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com