Storing Business Hours in a Database

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.