问题描述
我打算存储商店的工作时间.我想知道什么可能是工作时间领域的最佳建模,以便我能够以非常有效的方式获得当前开放/关闭的商店列表.
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.
推荐答案
要存储正常的营业时间,您需要存储一些包含以下内容的记录:
To store normal operation hours, you would need to store a number of records containing:
- 商店 - INTEGER
- DayOfWeek - 整数 (0-6)
- 开放时间 - 时间
- 关闭时间 - 时间
例如,我假设每个商店在国定假日期间减少了营业时间,或者工厂停工,因此您还需要存储一些覆盖记录:
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:
- 商店 - INTEGER
- 覆盖开始日期 - 日期
- 覆盖结束日期 - 日期
- DayOfWeek - 整数 (0-6)
- AltOpenTime - 时间
- AltCloseTime - 时间
- 关闭 - 整数 (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.
您可以在这里执行一些不错的 SQL-fu,但这为您提供了基础知识.
There may be some nice SQL-fu you can do here, but this gives you the basics.
编辑
我还没有测试过这个,但这应该会让你接近:
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))
编辑
至于效率,从某种意义上说,它是高效的,您只需调用一次 MySQL,如果它跨网络,这通常是一个瓶颈.您必须进行测试,看看这是否符合您的规格.如果没有,你可能会玩一些索引.
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.
编辑
测试.不是完整的测试,但有一些.
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)
这篇关于存储工作时间和高效查询的最佳方式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!