idOle
asked on
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.
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.
ASKER
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.
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.
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.
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Wow, that's very enlightening. Thanks a lot.
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.
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.