Query biz hours

Hello,

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.
 
idOleAsked:
Who is Participating?
 
smidgie82Connect With a Mentor Commented:
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.
0
 
smidgie82Commented:
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.
0
 
idOleAuthor Commented:
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.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
smidgie82Commented:
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
);

CREATE TABLE hours
(
  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:


SELECT name
FROM restaurants
WHERE id IN
(
  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.
0
 
idOleAuthor Commented:
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?

Thanks.
0
 
idOleAuthor Commented:
Wow, that's very enlightening. Thanks a lot.
0
 
smidgie82Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.