September 9, 2009 at 4:23 am
Hi all,
I need to create a table which can hold information about when a service is unavailable. I have a table containing a collection of all the services we have on our system, but now we need to add another layer of complexity where we now want to hold the services unavailability time frames and dates.
Basically, a service can be continuously available or unavailable at certain times. In our 'Service' table we have a column 'ContinuousAvailability', so a bool tells us whether a service is always online or not. If not, then th 'ServiceAvailability' table comes into it, and here we want to have the time spans for which a service is unavailable (for example, Sunday nights between 19:00 - 23:00). So from this I guess I need StartTime and EndTime columns, but how do I handle the 'Day' (Sunday) data, does 'Day' become a column as well?
Also, if a service is unavailable during certain dates, how does this factor into the table design - this is the bit that throws me?
Any help or direction on this would be appreciated.
Thanks
Tryst
September 15, 2009 at 7:08 am
I had a similar problem with a project I was working on. I opted to use an additional table to join against that defined the day schedules for a week. You can use any combination of days you might need and it can be managed in a single referential table. For example, the table might look like this:
ID Value
1 Monday
2 Tuesday
3 Wednesday
4 Thursday
5 Friday
6 Saturday
7 Sunday
8 Weekdays
9 Weekends
10 Monday,Wednesday,Friday
Hope this helps 🙂
September 15, 2009 at 3:09 pm
You could have 3 fields: starttime,endtime,dayofweek.
If dayof week is populated then this would indicate a recurring unavailability. If not populated then it is the specific dates in starttime and endtime.
Starttime and endtime would be datetime fields.
If dayofweek is populated then you would be effectively ignoring the date in starttime and endtime and just using the times from this field.
6/22/2009 3:00:00 PM,6/22/2009 4:00:00 PM,1 --indicates service is unavailable every sunday from 3-4
6/23/2009 8:00:00 PM,6/23/2009 9:00:00 PM, --indicates the service is unavailable only on 6/23 from 8-9
Note: I am putting the numerical day of the week in field dayofweek.
September 17, 2009 at 3:41 am
I think that you dont need "dayofweek" column. StarTDatetime and EndDateTime should be enough. You can get the day(s) of the week from these dates.
Dayofweek column is in a way redundant. As you pointed out one service is offline for some time every sunday. So to find out "which" sunday you need co-relate "dayofweek" with the startdatetime and enddatetime.
"Keep Trying"
September 17, 2009 at 11:40 am
I think the OP wanted to distinguish between recurring and one-time unavailabilities.
I see your point though. There could really be a type field with the choices being 'recurring' and 'one-time'.
I could also see this being done with just StartDatetime and EndDateTime. For recurring unavailabilities you would have to insert a record for every StartDatetime/DateTime range into the future. If the recurring unavailability changed you would have to update all of these records.
September 18, 2009 at 1:47 am
Yes you are right.
"Keep Trying"
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply