Storing Business Hours in a Database

Solution 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.

Solution 2:

One situation that isn't covered by this schema is several opening periods in a day. For example, the local pub is open 12:00-14:30 and 17:00-23:00.

Maybe a theatre box office is open for a matinee and an evening performance.

At that point you need to decide if you can have several entries for the same day, or if you need to represent different hours in the same row.

What about opening times that cross midnight. Say a bar is open 19:00-02:00. You couldn't just compare the opening and closing times with the time you want to test.