Query biz hours

Posted on 2006-06-05
Medium Priority
Last Modified: 2013-12-12

Here is what I plan to do:

I have a list of restaurants and I want the user to be able to query the restaurants by searching a specific time slot that a restaurant is open. For example, the app can return all the restaurants that open after 10 p.m. on Thursday or open on Sundays.

What is the right way to do the database design so that I can accomplish the goal? Thanks.
Question by:idOle
  • 4
  • 3

Expert Comment

ID: 16836316
Hi idOle,
Depends on how complicated you want the time specification to be.  Do you want restaurants to specify that they're open, say, 8:00 am to 11:00 pm?  What about 6:00 am to 11:00 am, closed from 11:00 am to 5:00 pm, open 5:00 pm to 11:00pm?  Or three distinct time slots, for breakfast, lunch, and dinner?  What about three time slots on Monday, all day Tuesday, dinner Wendesday, lunch and dinner Sunday, open late Thursdays, and 11:00 am to 9:00 pm Fridays and Saturdays?  You can see that it becomes even more complicated if you include special hours based on day of the month, etc.

So, first you need to define borders for the functionality of your search engine and the complexity of your data set.  Then, build your database based on that.

Author Comment

ID: 16836763
Hello smidgie82,

I'd like to simplify things for now. Let's say all the hours for a specific day is the same throughout the year. I don't want the restaurants to specify time slots for breakfast, lunch or dinner. But I do want them to specify the hours they are open for each of the 7 days in a week. E.g., open 6am-11am, 5pm-11pm.

Do I need 4x7 columns to serve this purpose? E.g., one for open time 1 for monday, one for close time 1, one for open time 2, one for close time 2, etc.? It would be weird that a restaurant has more than 2 seperate open time slots within one day, isn't it? But what if it Is true for a specific restaurant? I mean theoretically I do I handle this situation in my DB design?

Thanks a lot.

Expert Comment

ID: 16836899
Here's one option:

CREATE TABLE restaurants
  id INTEGER PRIMARY KEY, -- Or, alternatively, can use a SERIAL if you want keys to be auto-generated
  name TEXT

  id INTEGER PRIMARY KEY, -- or serial, as above
  restaurant INTEGER REFERENCES restaurants(id),
  day INTEGER, -- e.g., Sunday = 1, Saturday = 7, 0 for all, or any other scheme you want to devise
  open_time TIME, -- or whatever other format you want to use
  close_time TIME

Then, you can specify any number of day, time-interval pairs and associate them with your restaurant. Then a search can be:

FROM restaurants
  SELECT restaurant
  FROM hours
  WHERE (day = 1)         -- Open on Sundays
  AND (close_time > TEN_OCLOCK) -- open past ten.  Need to devise a constant TEN_OCLOCK, or replace with the appropriate value for comparison

I haven't tested the code, but the design oughta work.  Just make sure to create the right indexes to speed up lookups.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 16837213
Here is the thing that still confuses me:

How do I specify in the above table 'hours' for a restaurant that opens 11 am - 3 pm and 5- 9 pm Wednesday through Friday?


Accepted Solution

smidgie82 earned 500 total points
ID: 16837400
Try this:

INSERT INTO restaurants(id, name) VALUES (1, 'Taco Bell');

Now, to specify that this restaurant is open 11am - 3 pm on Monday, we do the following:

INSERT INTO hours (id, restaurant, day, open_time, close_time)
VALUES (1, -- because this is the first item in the hours table.  We'll call the next entry 2
1, -- because we want to talk about 'Taco Bell', which has id = 1 in the restaurants table
2, -- because in the above model, Sunday = 1 and Monday = 2
'11:00 am', -- open time
'3:00 pm' -- close time

And the afternoon time:
INSERT INTO hours(id, restaurant, day, open_time, close_time)
VALUES (2, -- just a unique identifier for this entry
1, -- still talking about Taco Bell
2, -- Still talking about Monday
'5:00 pm', -- open time
'9:00 pm' -- close time

Do the same thing for Tuesday, Wednesday, Thursday, and Friday.

Note, this isn't the only way you could do this.  It's just the first thing that occurred to me, and it WILL work.  Whether it's the best / fastest / most robust / most scalable way...  That would take testing, as well as an in-depth knowledge of the sort of queries that are likely to be run against it.  On the bright side, if most queries will be looking up times, you can do a B-tree index on open and close time, and hash or B-tree index on hours.id, and hash or B-tree index on day, and queries of the time "Open past ten," "Open on Saturdays", etc. should be pretty quick.

Author Comment

ID: 16837535
Wow, that's very enlightening. Thanks a lot.

Expert Comment

ID: 16837688
My pleasure.  And now, the best thing you can do is to experiment.  Populate it with random data and run a gazillion queries.  See where it breaks down.  Use EXPLAIN and any other profiling and optimization tools you can find.  Come up with another method, implement it, and compare it to this one.  Use the best parts of each.  You'll find out things just by playing that you'll use later and wouldn't have known otherwise.

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

These days socially coordinated efforts have turned into a critical requirement for enterprises.
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses
Course of the Month16 days, 11 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question