What is a good approach for Database design for hours of operation?

Question

I am trying to create an application that will need to use hours of operation and allow users to search by it (think how Yelp does this).

I am debating what is a good design for this. The only thing I can think of is having a database table with a foreign key to a "Companies" table that lists a day of the week and the open and close times.

Tbl_Hours_Operation
- day_of_week
- open_time
- close_time
- company_id

Is there any other approach that would work and be more efficient?

Answer 1

Have two tables:

operational_hours (company_id, day_of_week, open_time, close_time)

operational_hours_special (company_id, date, open_time, close_time)

You would need to join the two tables to check for the special hours.

Will any of your companies be closed for breakfast, lunch, dinner, siesta? If so, I would add:

operational_hours_closed (company_id, day_of_week, close_time, open_time)

Even more fun JOINs!