What is a good approach for Database design for hours of operation?
database, database-design, sql
Solution
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 `JOIN`s!
Problem
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?