What is the best way to store 'hours of operation' in a database? We have 21 branches, and the hours for each vary quite a bit. I don't want to just hard code it, because I need the information in more than one location...not to mention for the map application api. Should I just store the information as a long string of text, html line breaks included? Is there a better way?

Make branch and time part of the key:

OperatingTimes {BranchNum, StartTime, EndTime} KEY {BranchNum, StartTime};

I assume that your branches work the same schedule every week, but different hours on different days. If so, you have two possible solutions.

The "all encompassing" solution is to store open and close times by calendar day. This allows you to easily deal with holidays, summer/winter hours, and special events (such as a branch being open late on the first or the third of every month). It uses more disk (rows), but it is very simple and it allows you nearly infinite flexibility.

The "week with exceptions" allows you to record a "standard" week schedule, then only record the days that are non-standard. This is a bit more complicated, but it uses a lot less storage.

The "week without exceptions" allows you to store one row with branch, day-of-week, open time, and close time. This is very efficent in terms of space, but doesn't allow for exceptions (no holidays, etc).

The "same schedule every day" records branch, open time and close time. It assumes that your branch observes the same schedule every day with no exceptions. This is what I think that dportas was discussing.

-PatP

Sorry, got sidetracked with a different project for a couple of days...

Thank you very much for your responses! They were most helpful. I think the 'week with exceptions' will work best in this situation. While branch hours vary widely between them, each branch has basically the same schedule all year. The only exceptions being two branches that have 'summer hours'. And, of course, holidays.

I'll try that one out. Thanks again for the responses!

--Alex

Probably easiest, from a software development perspective, to switch to being open 24 hours per day, seven days per week. Makes your customers happy too.