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.