Weekly Schedules - How can you store this in a database?

Question

Currently, I'm working on a project to manage maintenance windows on a database of servers, etc. Basically, I only need to be accurate down to the hour, but allow for them to be set to allow, or disallow, for each day of the week.

I've had a few ideas on how to do this, but since I work by myself, I'm not wanting to commit to anything without some feedback.

To visualize this, it's something like the flowing "graph"

    | Sun | Mon | Tue | Wed | Thu | Fri | Sat |
    -------------------------------------------
5AM |allow|allow|allow|deny |deny |allow|allow|
    -------------------------------------------
6AM |allow|deny |deny |deny |deny |deny |allow|
    -------------------------------------------
7AM |allow|deny |deny |deny |deny |deny |allow|
    -------------------------------------------
8AM |allow|deny |deny |deny |deny |deny |allow|
    -------------------------------------------
9AM |allow|deny |deny |deny |deny |deny |allow|
    -------------------------------------------
... etc...

Is there a standard way of doing this or a resource that might give me some ideas to...

1. Make a format that can be saved and restored easily
2. Make it searchable within the database (for example, not having to deserialize it to search for a time)

[Update]

It is worth mentioning that a day could, even though unlikely, be set to "allow, deny, allow, deny...etc...". The span isn't guaranteed to be the only one for the whole day.

This is also not the only schedule, there will be hundreds of devices each with their own schedule, so it's going to get hairy... lol??

Rob asked if each week needed to be tracked - It does not. This is a generic schedule that will apply to the entire year (regularly scheduled maintenance)

Answer 1

Maybe something like

TABLE:
   StartTime DATETIME      PrimaryKey,
   EndTime   DATETIME      PrimaryKey,  /*if you are positive it will be in one hour incerments then you might want to omit this one*/
   Monday    BIT,
   TuesDay   BIT,
   Wednesday BIT,
   Thursday  BIT,
   Friday    BIT,
   Saturday  BIT,
   Sunday    BIT

Answer 2

I would consider for (1) using a format that includes both start and end times, and an integer field for the day of the week. I know you stated the blocks will always be one hour, but that can be enforced by your code. Also, if your requirements change one day, you'll have a lot less to worry about in step (2) than if your DB statements are all written to assume 1 hour blocks.

CREATE TABLE maintWindow (
   maintWindowId  int primary key auto_increment not null,
   startTime      Time,
   endTime        Time,
   dayOfWeek      int,
   ...

For (2), if each record has a start and end time associated with it, then it's very easy to check for windows for any given time:

SELECT maintWindowId
FROM maintWindow
WHERE $time >= TIME(startTime) AND $time <= TIME(endTime) AND DAYOFWEEK($time) = dayOfWeek

(where $time represents the date and time you want to check).

The allowing or disallowing for each day of the week would be handled by separate records. IMHO, this is more flexible than hard-coding for each day of the week, since you'll then be using some kind of case statement or if-else switch to check the right DB column for the day you're interested in.

Note: Be sure you know which standard your DB uses for the integer day of the week, and try to make your code independent of it (always ask the DB). We've had lots of fun with different standards for the start of the week (Sunday or Monday) and the starting index (0 or 1).

Answer 3

If it is going to be different every week, then set up the table like this;

TABLE:
    StartTime DATETIME    PrimaryKey

If a start time for a particular date/hour is set, then assume that it is allowed, otherwise deny.

If it is a generic configuration for a generic week that doesn't change, try this;

TABLE:
    Hour  INT,
    Day   INT,
    Allow BIT

Then add rows for every hour/day combination.

Answer 4

I've actually used this design before, basically creating a bitmap for the time span you want to regularly schedule divided by the number of periods you want. So in your example you want a week schedule with hourly periods, so you'll have a 168 bit bitmap which is only 21 bytes long. A couple of datetimes are 16 bytes together and you'll need multiple rows of these to represent the possible schedules for a given week so if you care at all about size I don't think you can beat it.

I will admit it is a little trickier to deal with and less flexible than the previous suggestions. Consider if you all of a sudden wanted to use 1/2 hour periods, you would need to transcode all your existing data to a new 336 bit bitmap and distribute values out.

If you're using SQL, you can either store this as a binary blog and do the bit twiddling to compare whether a bit is on or off yourself or you can store each bit as a column. MS SQL Server supports up to 1024 for standard or 30k for wide tables so you could easily get granularity down to 10 minutes for either or a great deal finer for a 30k table.

I hope this adds a little different perspective on how it might be done. It's really only necessary if you are worried about space/size or if you have perhaps 10s or 100s of millions of them.

Answer 5

You could easily just record the "allowed" times in a table. That way, if it isn't there, it isn't allowed. If you need to have a more variable "schedule" you could easily add a year and month field.

 TABLE DBMaintSched
      ID int PK
      ServerID varchar(30) (indexed)
      Day int
      Month char(3)
      DayOfWeek char(3)
      Year int
      StartDT DateTime
      EndDT DateTime

For December, 2008:

 SELECT * FROM DBMaintSched WHERE ServerID = 'SQLSERVER01' AND Month = 'DEC' AND Year = 2008 ORDER BY DAY ASC

You have all the days for Dec 2008 on which maintenance can be performed. Display however you wish.