Availability on AppointmentCalendar class

[imagined]

I'm setting up with class with availability dates and times. So if there is a date and time that the user is unavailable, it won't be possible to make an appointment during that period.

The user should be able to set specific dates and times he won't be available, on times he had previously set to be available. For example, let say he's available every Monday from 8am-5pm, but there is this specific Monday he won't be, so he enters that specific date and it has precedence over the Availability. So for that I setup the following table:

Unavailability table with the following fields

id int not null auto_increment primary key
userid int not null
UnavailableStartDateTime datetime not null
UnavailableEndDateTime datetime not null


The user should be able to set normal schedules available dates and times. The user should be able to set something like
Available Every Monday from 8am - 5pm
Available Every Tuesday from 9am - 6pm
Available Every Wednesday from 8am - 12pm

So I setup the the Availability table with the following fields
id int not null auto_increment primary key
userid int not null
AvailableDay text (to enter Monday or Tuesday)
AvailableTimeStart time
AvailableTimeEnd time


Any suggestions?

[jwilliam]

Looks okay, but I'd probably drop the AvailableDay column in Availability. It's not really necessary because you can get that information from your timestamp TimeStart. As for "normal schedules" I'd probably make a column call "recurring" or something like that. Then you could store a value in it like "none," "weekly," "monthly," etc... I've been at work for 9 hours so this is all completely off the top of my head :)

[imagined]

I've been at work for 9 hours so this is all completely off the top of my head :)
Thanks for still taking the time to offer good advice.


As for "normal schedules" I'd probably make a column call "recurring" or something like that. Then you could store a value in it like "none," "weekly," "monthly," etc...
But timestamp doesn't store SPECIFIC dates?

So if the user enters Monday and "Mon" gets stored on the Day column. When the method is looping through the days and makes a check like date('D') == Mon, then sets that day as available.

I don't know maybe I'm wrong.

[jwilliam]

I just noticed you're using the TIME type. You could probably just ignore everything I said before :) Your table should work fine... the only thing is it doesn't account for specific availabilities. In other words, if I said I'm only available "November 25th, 2011 @ 8AM thru November 25th, 2011 @ 1PM," your table couldn't describe that. It may not need to though...