storing a schedule in mysql

Question

I am interested in a way to store the schedule of my users. I need to save the day of the week and the hours when he is available.Could be from 8:00 to 12, or from 9:00 to 12 and then from 15:00 to 18:00 in intervals.I'm not interested in fancy database operations i just need to add and get the dates out easily when needed.

Answer 1

I'm not interested in fancy database operations

Alarm bells are ringing in my head. Does that mean you're also not interested in analysing the problem fully before implementing it?

There are very different semantics associated with managing an unbounded schedule of repeating events with a well defined pattern (e.g. every tuesday from 9-12) vs. storing a fixed schedule (e.g. Tuesday 5th April 9-12). Obviously the latter cannot be done with an indefinite time span, although within a defined timeframe you can implement the former using the latter model.

If its just a matter of recording historical events then the data is bounded and therefore the second approach is more appropriate. But for future events you'll probably need both methods - but the former is difficult to implement on the database tier.

Answer 2

Here is a link to a website that solves common MySQL query problems, from these examples you can reverse engineer your structure.

http://www.artfulsoftware.com/infotree/queries.php#98

This one will help with "Available Appointments"

Answer 3

I modeled it like this:

int DayOfWeek // maps to System.DayOfWeek enum
time(0) Start // smallest possible time with seconds precision
time(0) End