determine if a restaurant is open now (like yelp does) using database, php, js

Question

i was wondering if anyone knows how yelp determines what restaurants are "open now"? i'm developing a similar application using html/javascript/php. i was going to have a column in my database for each day, with comma separated hours written in "2243" format (10:43 pm). so for example if a restaurant is open for lunch and dinner it might be "1100,1400,1700,2200". then i'd check (using js) if the current time falls in one of the ranges for the current day. i'd also like to be able to determine if a restaurant is "open tonight", "open late", etc. for those i guess i'd check whether the open range overlaps with certain ranges.

is there a better way to do this? particularly, how to store the hours in the database and then determine if they overlap with a given set of hours.

thanks.

Answer 1

I would have a table named REST_HOURS with the following fields:

CREATE TABLE REST_HOURS (
REST_ID integer NOT NULL
,OPEN_TIME time NOT NULL
,CLOSE_TIME time NOT NULL
PRIMARY KEY ( `REST_ID` )
) ;

index on OPEN_TIME and CLOSE_TIME

I would then do a query like:

select REST_ID from REST_HOURS where CURTIME() >= OPEN_TIME and CURTIME() <= CLOSE_TIME

of course you can replace CURTIME() with any time you want (You might want to add a day of week for open and close, since some restaurants have different hours for say, Sunday)

Answer 2

You definitely want to redesign the database. Putting multiple values into a single column like that is a huge violation of the rules of normalization and will cause lots of headaches down the road. A single column should always hold a single piece of information.

You should also be using proper data types. Don't put times in a string, because you could end up with "foo" as a time and then what do you do?

Instead, what you probably want is:

CREATE TABLE Restaurants
(
    restaurant_id    INT            NOT NULL,
    restaurant_name  VARCHAR(40)    NOT NULL,
    CONSTRAINT PK_Restaurants PRIMARY KEY CLUSTERED (restaurant_id)
)
CREATE TABLE Restaurant_Hours
(
    restaurant_id    INT         NOT NULL,
    hours_id         INT         NOT NULL,
    day_of_week      SMALLINT    NOT NULL,
    start_time       TIME        NOT NULL,  -- Depends on your RDBMS and which date/time datatypes it supports
    end_time         TIME        NOT NULL,
    CONSTRAINT PK_Restaurant_Hours PRIMARY KEY CLUSTERED (restaurant_id, hours_id)
)

You can then easily check for restaurants open at a given time:

SELECT
    R.restaurant_id,
    R.restaurant_name
FROM
    Restaurants R
WHERE
    EXISTS
    (
        SELECT *
        FROM
            Restaurant_Hours RH
        WHERE
            RH.restaurant_id = R.restaurant_id AND
            RH.start_time <= @time AND
            RH.end_time >= @time AND
            RH.day_of_week = @day_of_week
    )

If you have a time slot that spans midnight you would need to have two rows - one for the first day, and one for midnight - "x" for the next day. Also, remember to keep time zones in mind when using from a GUI.