51

I'm planning to store working hours for shops. I'm wondering what might be the best modeling for the working hours field so that I can get a list of open/closed shops at the current moment in very efficient way.

Improve this question
0

2 Answers 2

Reset to default
84

To store normal operation hours, you would need to store a number of records containing:

  • Shop - INTEGER
  • DayOfWeek - INTEGER (0-6)
  • OpenTime - TIME
  • CloseTime - TIME

I assume for example that each shop has reduced hours during national holidays, or has plant shutdowns, so you would also need to store some override records:

  • Shop - INTEGER
  • OverrideStartDate - DATE
  • OverrideEndDate - DATE
  • DayOfWeek - INTEGER (0-6)
  • AltOpenTime - TIME
  • AltCloseTime - TIME
  • Closed - INTEGER (0, 1)

To find open shops is trivial, but you also need to check if there are override hours:

SELECT Shop FROM OverrideHours WHERE OverrideStartDate <= NOW() AND OverrideEndDate >= NOW() AND DayOfWeek = WEEKDAY(NOW())

If there are any record returned, those shops have alternate hours or are closed.

There may be some nice SQL-fu you can do here, but this gives you the basics.

EDIT

I haven't tested this, but this should get you close:

SELECT Normal.Shop FROM Normal LEFT JOIN Override ON Normal.Shop = Override.Shop AND Normal.DayOfWeek = Override.DayOfWeek AND NOW() BETWEEN Override.OverrideStartDate AND Override.OverrideEndDate WHERE Normal.DayOfWeek = WEEKDAY(NOW()) AND ((Override.Shop IS NULL AND TIME(NOW()) BETWEEN Normal.OpenTime AND Normal.CloseTime) OR (Override.Shop IS NOT NULL AND Override.Closed <> 1 AND TIME(NOW()) BETWEEN Override.AltOpenTime AND Override.AltCloseTime))

EDIT

As for efficiency, it is efficient in the sense that you only have to make one call to MySQL which is often a bottleneck if it is across a network. You'll have to test and see whether this performs to your specifications. If not, you may be to play with some indices.

EDIT

Testing. Not complete testing, but some.

mysql> select * from Normal; +------+-----------+----------+-----------+ | Shop | DayOfWeek | OpenTime | CloseTime | +------+-----------+----------+-----------+ | 1 | 1 | 09:00:00 | 17:00:00 | | 1 | 5 | 09:00:00 | 16:00:00 | | 2 | 1 | 09:00:00 | 17:00:00 | | 2 | 5 | 09:00:00 | 17:00:00 | +------+-----------+----------+-----------+ 4 rows in set (0.01 sec) mysql> select * from Override; +------+-------------------+-----------------+-----------+-------------+--------------+--------+ | Shop | OverrideStartDate | OverrideEndDate | DayOfWeek | AltOpenTime | AltCloseTime | Closed | +------+-------------------+-----------------+-----------+-------------+--------------+--------+ | 2 | 2010-12-01 | 2010-12-31 | 1 | 09:00:00 | 18:00:00 | 0 | | 2 | 2010-12-01 | 2010-12-31 | 5 | 09:00:00 | 18:00:00 | 0 | | 1 | 2010-12-01 | 2010-12-31 | 1 | 09:00:00 | 17:00:00 | 1 | +------+-------------------+-----------------+-----------+-------------+--------------+--------+ 3 rows in set (0.00 sec) mysql> SET @whenever = TIMESTAMP('2010-11-23 16:05'); Query OK, 0 rows affected (0.00 sec) mysql> SELECT WEEKDAY(@whenever); +--------------------+ | WEEKDAY(@whenever) | +--------------------+ | 1 | +--------------------+ 1 row in set (0.00 sec) mysql> SELECT Normal.Shop FROM Normal LEFT JOIN Override ON Normal.Shop = Override.Shop AND Normal.DayOfWeek = Override.DayOfWeek AND @whenever BETWEEN Override.OverrideStartDate AND Override.OverrideEndDate WHERE Normal.DayOfWeek = WEEKDAY(@whenever) AND ((Override.Shop IS NULL AND TIME(@whenever) BETWEEN Normal.OpenTime AND Normal.CloseTime) OR (Override.Shop IS NOT NULL AND Override.Closed <> 1 AND TIME(@whenever) BETWEEN Override.AltOpenTime AND Override.AltCloseTime)); +------+ | Shop | +------+ | 1 | | 2 | +------+ 2 rows in set (0.00 sec) mysql> SET @whenever = TIMESTAMP('2010-11-23 17:05'); Query OK, 0 rows affected (0.00 sec) mysql> SELECT Normal.Shop FROM Normal LEFT JOIN Override ON Normal.Shop = Override.Shop AND Normal.DayOfWeek = Override.DayOfWeek AND @whenever BETWEEN Override.OverrideStartDate AND Override.OverrideEndDate WHERE Normal.DayOfWeek = WEEKDAY(@whenever) AND ((Override.Shop IS NULL AND TIME(@whenever) BETWEEN Normal.OpenTime AND Normal.CloseTime) OR (Override.Shop IS NOT NULL AND Override.Closed <> 1 AND TIME(@whenever) BETWEEN Override.AltOpenTime AND Override.AltCloseTime)); Empty set (0.01 sec) mysql> SET @whenever = TIMESTAMP('2010-12-25 16:05'); Query OK, 0 rows affected (0.00 sec) mysql> SELECT Normal.Shop FROM Normal LEFT JOIN Override ON Normal.Shop = Override.Shop AND Normal.DayOfWeek = Override.DayOfWeek AND @whenever BETWEEN Override.OverrideStartDate AND Override.OverrideEndDate WHERE Normal.DayOfWeek = WEEKDAY(@whenever) AND ((Override.Shop IS NULL AND TIME(@whenever) BETWEEN Normal.OpenTime AND Normal.CloseTime) OR (Override.Shop IS NOT NULL AND Override.Closed <> 1 AND TIME(@whenever) BETWEEN Override.AltOpenTime AND Override.AltCloseTime)); +------+ | Shop | +------+ | 2 | +------+ 1 row in set (0.00 sec) mysql> SET @whenever = TIMESTAMP('2010-11-23 17:05'); Query OK, 0 rows affected (0.00 sec) mysql> SELECT WEEKDAY(@whenever); +--------------------+ | WEEKDAY(@whenever) | +--------------------+ | 1 | +--------------------+ 1 row in set (0.00 sec) mysql> SELECT Normal.Shop FROM Normal LEFT JOIN Override ON Normal.Shop = Override.Shop AND Normal.DayOfWeek = Override.DayOfWeek AND @whenever BETWEEN Override.OverrideStartDate AND Override.OverrideEndDate WHERE Normal.DayOfWeek = WEEKDAY(@whenever) AND ((Override.Shop IS NULL AND TIME(@whenever) BETWEEN Normal.OpenTime AND Normal.CloseTime) OR (Override.Shop IS NOT NULL AND Override.Closed <> 1 AND TIME(@whenever) BETWEEN Override.AltOpenTime AND Override.AltCloseTime)); Empty set (0.00 sec)
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Great answer! Quick question: Would it be advisable to place indexes on Shop, DayOfWeek, OpenTime AND CloseTime?
@RabidFire, see the answers to stackoverflow.com/questions/3049283/… . If the primary efficiency you're looking for is querying (as opposed to insertion, for example), then adding an index to any field used in a WHERE or JOIN ON should speed up the search. You'll have to test to see which one's are the most effective.
@Antoniossss, You could do multiple entries per day... day 1 17:00-11:59:59, day 2 00:00-3:00, day 2 17:00-11:59:59, day 3 00:00-3:00, etc... your code will just have to be such that it expects multiple time entries per day. Or, depending on your needs, you may have to develop a different model.
@RobertGowland Could you please tell what do you mean by AltOpenTime and AltCloseTime? Someone need to know it :)
@Sami and @Belle , "each shop has reduced hours during national holidays, or has plant shutdowns" and that's purpose of the Override table. The "alt" in the column name does indeed stand for "alternative". As you can see from the query, if there is an entry in the Override table, the hours from that table are used instead.
|
9

Let's consider all opening hours are the same every week. So what about following table:

  • shop_id - INTEGER (or any unique identifier of the shop)
  • week_day - INTEGER (0=Monday - 6=Sunday)
  • opens_at - TIME (use your local timezone)
  • closes_at - TIME (use your local timezone)

Make a table for shops identified by shop_id and then insert the opening hours, i.e:

  • 1, 0, 8:00, 17:00
  • ...
  • 1, 5, 8:00, 12:00
  • 2, 0, 7:30, 12:30
  • 2, 0, 13:30, 17:30
  • 2, 1, 7:30, 12:30
  • 2, 1, 13:30, 17:30
  • ...

and then SELECT:

SELECT shop_id FROM opening_hours WHERE WEEKDAY(NOW()) = week_day AND TIME(NOW()) BETWEEN opens_at AND closes_at
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.