在数据库中存储各种商店开业时间的方法 [英] Way to store various shop opening times in a database

问题描述

我想在数据库中存储不同商店的营业时间。目前我正在使用最简单的解决方案：

I want to store opening times for different shops in a database. At the moment I am working with the simplest solution:

CREATE TABLE opening_times(
 shop_id int(3) NOT NULL,
 times varchar(1000) NOT NULL
);

INSERT INTO opening_times VALUES(3,"Mon-Fri 8:30 to 18:00
Sat 9:00 to 12:00");

INSERT INTO opening_times VALUES(4,"24/7");

INSERT INTO opening_times VALUES(5,"Mon-Sun 8am-8pm");

我的下一个想法是：

CREATE TABLE opening_times(
 shop_id int(3) NOT NULL,
 monday varchar(11) NOT NULL, 
 tuesday varchar(11) NOT NULL, 
 wednesday varchar(11) NOT NULL, 
 thursday varchar(11) NOT NULL, 
 friday varchar(11) NOT NULL, 
 saturday varchar(11) NOT NULL, 
 sunday varchar(11) NOT NULL
);

INSERT INTO opening_times VALUES(
 3,
 "09:30-18:30",
 "09:30-18:30",
 "09:30-18:30",
 "09:30-18:30",
 "09:30-18:30",
 "09:30-12:30",
 "CLOSED"
);

但这仍然会导致一些问题：

But this still leads to some problems:

• 不能使用多次的天数。 （上午8时至上午11时至下午1时至下午6时）


• 有许多未使用的/冗余的字段，因为我的很多数据集每天都有固定的时间或全天候开放。


• 不容易搜索。


• 无法表示节假日。

• Days with multiple times are not possible. (8am to 11am & 1pm to 6pm)
• There is a lot of unused/redundant fields as many of my data sets have fixed times every day or are open 24/7
• They are not easy searchable.
• Holidays can not be represented.

所以现在我想知道是否有一个灵活的方式来存储开放时间。也许在像 WD [1-5] {8-18}，WD [6] {8-14}，CD [12 / 25-12 / 26] {！0-24} / code>其中 WD 表示工作日， CD 表示日历日或范围， c $ c>！表示已关闭。

So now I am wondering if there is a flexible way to store opening times. Maybe in a syntax like WD[1-5]{8-18},WD[6]{8-14},CD[12/25-12/26]{!0-24} where WD means weekday and CD represents a calender day or a range for exceptions and ! means closed.

有一种常见的方式来存储信息此？

Is there a common way to store the information like this?

推荐答案

规范化数据

它为

shop_ID, Weekday, Start_hour, end_hour

工作日可以有1到7之间的值，作为

weekday can have values between 1 and 7, as an output of

SELECT DAYOFWEEK('2007-02-03')

开始小时和结束小时可以存储在时间< a href =http://dev.mysql.com/doc/refman/5.0/en/time.html> http://dev.mysql.com/doc/refman/5.0/en/time.html < a>

start hour and end hour can be stored in time http://dev.mysql.com/doc/refman/5.0/en/time.html

这样你就能拥有一切。

do

select start_hour, end_hour from table where weekday=dayofweek(curdate()) and shop_id=1

一个商店需要两天的时间间隔？没有问题，

Need 2 time intervals for a day for a shop? no problem,

`shop ID, weekday, start_hour, end_hour`
1;         1;       08:00:00 ; 09:00:00
1;         1;       10:00:00 ; 11:00:00

对于异常，可以添加异常表与日期和商店。你可以查询，如果它为null（没有例外），返回营业时间。或者，您可以存储每个商店的每个日期，但这会膨胀您的数据。

For exceptions, you can add an exceptions table with the date and the shop. You can query that, and if it's null(no exception), return opening hours. Alternatively you can store every date for every shop, but that would bloat your data.

这篇关于在数据库中存储各种商店开业时间的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！