Christopher tm Herdt (cherdt) wrote in databases,
Christopher tm Herdt
cherdt
databases

  • Music:

Building Hours/Hours of Operation

I'm trying to figure out best to handle building hours in a DB. The buildings all have default hours (e.g. M-F 8:00 - 19:00, Sa. 10:00 - 17:00, Su. closed), but may also have exceptions to the posted building hours, e.g. during holidays it may have shortened hours or no open hours at all.


Should I create separate tables for default hours and exceptions, e.g.:

Default Hours Table
Building_ID
Day_of_Week_ID
Open_Time
Close_Time



Exceptions Table
Building_ID
Start_Date
End_Date
Open_Time
Close_Time



I feel like I'm overlooking a solution that is elegant, simple, and obvious—I'm willing to settle for any one of the three.
  • Post a new comment

    Error

    Anonymous comments are disabled in this journal

    default userpic

    Your IP address will be recorded 

  • 8 comments
In the "hours" table, have:

Building_ID
Day_of_Week_ID
Open_Time
Close_Time
Valid_Start
Valid_End

Set the "Valid_Start" and "_End" to "0000-00-00" for the default values. Otherwise, put in the start date and end date for the exceptional hours. (eg, 2007-12-25 to 2007-12-25.) Then to display the building's hours for today, your query can be (roughly)

"where Building_ID ='nnn' and (Valid_Start='0000-00-00' or (CURDATE() >= Valid_Start and CURDATE() <= Valid_End)) order by Valid_End desc limit 1"

(which will get the default unless there's an exception)

You can also query for the exceptions for the next month or three months or whatever. Easy-Peasy.
Oh, it might be easier just to have a Valid_Date rather than _Start and _End, since you're making a separate record for each day of the week anyway. Then:

select fields from table where Building_ID ='nnn' and (Valid_Date='0000-00-00' or CURDATE() = Valid_Date) order by Valid_Date desc limit 1
"CurDate()" and "Limit 1" are both specific to MySQL, but there are equivilants in other systems. Depending on what you're trying to do with it, a date argument and "Where Exists" might be more appropriate, anyway.

I'm not very experienced with MySQL, but this database I've been reading the last month or two does have DateTime fields, so there are at least some versions of MySQL with support for them. You can't put "0000-00-00" in a DateTime, but I think a null would be more appropriate, anyway.
In a datetime in MySQL, the zero value is "0000-00-00 00:00:00". You can also have a plain date field, though, which is "0000-00-00".

I think NULL might mess up the sorting (doesn't NULL always sort first or always last?), and I favor "0" for "no, I mean that to be 0!" and NULL for "ack! I don't know what value goes there!", but whatevs, I don't have any rational objections to that. If NULL floats your boat, go for it.

I didn't actually ask what the database system was; the OP is going to have to change syntax and all that based on software and version. This was just a sketch.
I'm not sure about Informix or Oracle (the other DB's I've used), but in MS SQL Server, there's no such thing as a zero date. It doesn't really make sense to call a date 0, anyway, does it? Having a value like "0000-00-00" to show an explicit lack of a date could be handy, though, even if a keyword would be better than a magic value.

In SQL Server, null sorts first (in ascending). I would think all zeros would sort the same way, but after null. Yes, null is supposed to mean unknown, and you're going to be fighting three-valued logic if you use it for something else.

Looking again, I think your basic approach will work with a "Select Top 1" in SQL Server or a "Select First 1" in Informix (as long as you're only looking for one day), but something else entirely would have to be done in Oracle. There, it would probably be easiest to just do something procedural in PL/SQL.
I think this is how I'd do it:

Select Days.Date_Value, Coalesce(OverRideHours.Open_Time, DefaultHours.Open_Time) As Open_Time, Coalesce(OverRideHours.Close_Time, DefaultHours.Close_Time) As Close_Time From Days -- There has to be some day table to allow the scheduling Join Hours As DefaultHours On Hours.Valid_Start Is Null -- or "= '0000-00-00'" And Days.Day_Of_Week_ID = Hours.Day_Of_Week_ID Left Join Hours As OverRideHours On Days.Date_Value >= Hours.Valid_Start And Days.Date_Value <= Hours.Valid_End And Hours.Valid_Start Is Not Null -- or "<> '0000-00-00'"
The most important question seems not to have been asked:

What are you trying to do with the data?
In this case, we're trying to set up a room scheduling system that will let users reserve rooms only for times within operating hours. We may also use it to post daily building hours on digital signage.