Storing Business Hours in a Database

Question

I'm currently trying to work out the best way to store a business' hours of operation in a database.

For example:

Business A has the following hours of operation

• Monday: 9am - 5pm
• Tuesday: 9am - 5pm
• Wednesday: 9am - 5pm
• Thursday: 9am - 5pm
• Friday: 9am - 5pm
• Saturday: 9am - 12 Midday
• Sunday: Closed

Currently I'm have a data model similar to the following

CREATE TABLE "business_hours" (
    "id" integer NOT NULL PRIMARY KEY,
    "day" varchar(16) NOT NULL,
    "open_time" time,
    "close_time" time
)

where the "day" is restricted to a choice of the 7 days of the week in code (through the ORM). To test if a business is closed on a certain day it checks if the open_time and close_time are NULL. It is related to the business through a intermediate table (Many To Many Relationship).

Does any one have any suggestions for this database scheme? Something about it doesn't seem right to me.

Answer 1

Overall, I see nothing wrong with this. Except...

1. I would store the day of week as an integer using whatever numbering system your native programming language uses (in its libraries). This will decrease the size of the database and remove string comparisons from your code.

2. I would probably put the foreign key to the business table right here in this table. That way you won't need a link table.

So I guess I would do:

CREATE TABLE "business_hours" (
     "id" integer NOT NULL PRIMARY KEY,
     "business_id" integer NOT NULL FOREIGN KEY REFERENCES "businesses",
     "day" integer NOT NULL,
     "open_time" time,
     "close_time" time
)

In my business logic, I would enforce a constraint that every "business" has at least 7 "business hours". (At least because Jon Skeet is right, you might want holiday hours.) Though you may want to relax this constraint by simply leaving off "business hours" for days that the business is closed.

Answer 2

It sort of depends on what you need to store it for and what the real-world data could look like.
If you need to be able to determine if the business is open at a certain point then it may be a bit awkward to query the scheme as laid out. More importantly, though, is: Would you ever need to cater for a mid-day closure?

Some options include;

• A scheme like what you have, but with the option to have multiple periods for the same day. It would cater for the lunch break, but would make it awkward to run a query that gives you the opening hours for a given day, say for presentation to a user.
• A bitmap style approach; "000000000111111110000000" for 9-5. The downside to this approach is that you have to choose a specific granularity, i.e. whole hours or half-hours or, indeed, minutes. The finer the granularity, the harder the data is to read for a human. You could use bitwise operators to store this value as a single number rather than a string of integers, but again it hurts legibility.

Answer 3

Might think about factoring in holidays by including additional fields for month of year/day of month/week of month. Week of month has some minor subtlties "last" could for example be week 4 or 5 depending on the year.