Query biz hours

Posted on 2006-06-05
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
    LVL 9

    Expert Comment

    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

    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.
    LVL 9

    Expert Comment

    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.

    Author Comment

    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?

    LVL 9

    Accepted Solution

    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, 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

    Wow, that's very enlightening. Thanks a lot.
    LVL 9

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    As this topic comes over and over again in different forms, I've finally decided to write a short (yea, right...) article / tutorial about pagination with PHP with MySQL database. There are dozens of these kind of tutorials, I know - I wanted to mak…
    Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
    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.
    The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

    758 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now