Najlepszy sposób na przechowywanie godzin pracy i efektywne zapytanie

Question

Najlepszy sposób na przechowywanie godzin pracy i efektywne zapytanie

Planuję przechowywać godziny pracy dla sklepów. Zastanawiam się, jakie może być najlepsze modelowanie w obszarze godzin pracy, aby w bardzo efektywny sposób uzyskać listę sklepów otwartych/zamkniętych w danym momencie.

31

mysql sql database database-design

Author: Conrad Frix, 2010-12-16

Source

2 answers

Weźmy pod uwagę wszystkie godziny otwarcia są takie same co tydzień. Więc co z poniższą tabelą:

shop_id-INTEGER (lub dowolny unikalny identyfikator sklepu)
week_day-INTEGER (0=poniedziałek - 6 = niedziela)
opens_at-TIME (użyj lokalnej strefy czasowej)
closes_at-TIME (użyj lokalnej strefy czasowej)

Zrób tabelę dla sklepów oznaczonych przez shop_id, a następnie wprowadź godziny otwarcia, tzn.:

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

A następnie wybierz:

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

9

Author: eumiro,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-12-16 20:16:36

score 48 · Accepted Answer

Aby zapisać normalne godziny pracy, musisz zapisać kilka rekordów zawierających:

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

Zakładam na przykład, że każdy sklep ma skrócone godziny w święta narodowe, lub ma wyłączenia instalacji, więc trzeba również przechowywać niektóre rekordy override:

Shop-INTEGER
OverrideStartDate-data
OverrideEndDate - Data
DayOfWeek-INTEGER (0-6)
AltOpenTime-czas
AltCloseTime-czas
Closed - INTEGER (0, 1)

Aby znaleźć otwarte sklepy jest banalne, ale musisz również sprawdzić, czy są nadpisane godziny:

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

Jeśli są jakieś zapisy zwrócone, sklepy te mają alternatywne godziny lub są zamknięte.

Może być jakiś fajny SQL-fu, który możesz tutaj zrobić, ale to daje podstawy.

EDIT

Nie testowałem tego, ale to powinno cię zbliżyć:

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

Jeśli chodzi o wydajność, jest ona wydajna w tym sensie, że wystarczy wykonać tylko jedno połączenie do MySQL, które często jest wąskim gardłem, jeśli jest w sieci. Będziesz musiał przetestować i sprawdzić, czy działa to zgodnie ze specyfikacjami. Jeśli nie, możesz grać z niektórymi indeksami.

EDIT

Testowanie. Nie kompletne testy, ale niektóre.

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)