CODEWITHSUNDEEP
sqldatabasedatabase-design
user1840098
user1840098

Reputation: 99

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

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?

Upvotes: 9

Views: 3229

Answers (2)

JackAce
JackAce

Reputation: 1405

Your approach seems sound. Just a few minor things to note:

Make sure you index this table on Company_Id and DayOfWeek_Id (and in that order). It should also (possibly) support multiple entries per day of week, in case the company closes during the day or if it is open overnight.

CompanyDayOfWeek
----------------
Company_Id     INT/BIGINT    FK->Company table
DayOfWeek_Id   INT           (this can be a FK or just a hard coded list of IDs)
Open_Time      TIME          if your DB supports a dateless time data type
Close_Time     TIME

Upvotes: 0

Kermit
Kermit

Reputation: 34055

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!

Upvotes: 10

Related Questions