一家餐馆的网站有很多相关网站吗?

Each restaurant has restaurant branches, each branch must determine which days of the week it opens, each of those days must determine (several) open_hour and close_hour thru that day.

每家餐厅都有分店，每个分店都要确定营业时间，每一天都要确定当天的营业时间和营业时间。

I created one to many relationship using these tables: rest_names ---> rest_branches ---> open_days ---> open_hours

我使用这些表创建了一个到多个关系:rest_names——> rest_branch——> open_days——> open_hours

Am I going right this way? or there is another way to do this, maybe less complicated? And how the query will be like to get the hours of a restaurant on a specific day, say sunday?

我往这边走吗?或者有另一种方法，也许不那么复杂?以及查询将如何获得某一天(比如周日)餐馆的营业时间?

6 个解决方案

#1

one restaurant names to many branches
many branches to many open days
many open days to many open hours

一家餐厅有许多分店，许多分店有许多开放日，许多开放日，许多开放时间

select [columnnames]
from open_days
join rest_branches on open_days.rest_branchId = rest_branches.rest_branchId
join rest_name on rest_name.rest_nameId = rest_branch
where open_days = 'sunday'

在open_days上从open_days加入rest_branch中选择[columnnames]。rest_branchId = rest_branches。在rest_name上加入rest_name。rest_nameId = rest_branch，其中open_days = 'sunday'

#2

I would suggest storing the opening times, and then the time each restaurant stays open, instead of the closing time. This will make it easier to make calculations on the opening times, and it will also avoid the ambiguity with restaurants that stay open after midnight.

我建议把营业时间储存起来，然后每个餐厅的营业时间，而不是关门时间。这将使计算营业时间变得更容易，同时也将避免午夜之后营业的餐馆的模糊性。

Example design, using MySQL:

例设计,使用MySQL:

CREATE TABLE restaurant_chains (
    id int AUTO_INCREMENT PRIMARY KEY,
    name varchar(20)
);

CREATE TABLE restaurant_branches (
    id int AUTO_INCREMENT PRIMARY KEY,
    chain_id int NOT NULL,
    locality varchar(20)
);

CREATE TABLE opening_times (
    id int AUTO_INCREMENT PRIMARY KEY,
    branch_id int NOT NULL,
    day_of_week int NOT NULL,
    open_time time,
    open_duration time
);

Make sure to add the relevant foreign key and unique constraints. You could also add a check constraint so that day_of_week is always between 1 and 7, since that would represent the weekday (1 for Sunday, 2 for Monday, etc).

确保添加相关的外键和惟一约束。您还可以添加一个检查约束，以便day_of_week始终在1到7之间，因为这将表示工作日(1表示周日，2表示周一，等等)。

Now let's fill our database with some test data:

现在让我们用一些测试数据填充我们的数据库:

INSERT INTO restaurant_chains VALUES (NULL, 'Chain A');
INSERT INTO restaurant_chains VALUES (NULL, 'Chain B');

INSERT INTO restaurant_branches VALUES (NULL, 1, 'Branch 1 for A');
INSERT INTO restaurant_branches VALUES (NULL, 1, 'Branch 2 for A');
INSERT INTO restaurant_branches VALUES (NULL, 2, 'Branch 1 for B');
INSERT INTO restaurant_branches VALUES (NULL, 2, 'Branch 2 for B');

INSERT INTO opening_times VALUES (NULL, 1, 1, '10:00:00', '04:00:00');
INSERT INTO opening_times VALUES (NULL, 1, 1, '19:00:00', '03:00:00');
INSERT INTO opening_times VALUES (NULL, 1, 2, '08:00:00', '12:30:00');
INSERT INTO opening_times VALUES (NULL, 2, 1, '19:00:00', '05:15:00');
INSERT INTO opening_times VALUES (NULL, 2, 2, '19:00:00', '04:00:00');

The following query returns the opening time, closing time, and duration for all restaurants:

以下查询返回所有餐厅的开放时间、关闭时间和持续时间:

SELECT  rb.locality,
        ot.day_of_week,
        ot.open_time,
        ADDTIME(ot.open_time, open_duration) AS close_time,
        ot.open_duration
FROM    opening_times ot
JOIN    restaurant_branches rb ON (rb.id = ot.branch_id)
JOIN    restaurant_chains rc ON (rc.id = rb.chain_id);

+----------------+-------------+-----------+------------+---------------+
| locality       | day_of_week | open_time | close_time | open_duration |
+----------------+-------------+-----------+------------+---------------+
| Branch 1 for A |           1 | 10:00:00  | 14:00:00   | 04:00:00      | 
| Branch 1 for A |           1 | 19:00:00  | 22:00:00   | 03:00:00      | 
| Branch 1 for A |           2 | 08:00:00  | 20:30:00   | 12:30:00      | 
| Branch 2 for A |           1 | 19:00:00  | 24:15:00   | 05:15:00      | 
| Branch 2 for A |           2 | 19:00:00  | 23:00:00   | 04:00:00      | 
+----------------+-------------+-----------+------------+---------------+
5 rows in set (0.00 sec)

Then the following query would return the opening hours of a specific restaurant, on a specific day:

然后，下面的查询将返回特定餐厅在特定日期的营业时间:

SELECT  ot.open_time,
        DATEADD(ot.open_time, open_duration) AS close_time,
        ot.open_duration
FROM    opening_times ot
JOIN    restaurant_branches rb ON (rb.id = ot.branch_id)
JOIN    restaurant_chains rc ON (rc.id = rb.chain_id)
WHERE   rb.id = 1 AND ot.day_of_week = 1;

+-----------+------------+---------------+
| open_time | close_time | open_duration |
+-----------+------------+---------------+
| 10:00:00  | 14:00:00   | 04:00:00      | 
| 19:00:00  | 22:00:00   | 02:00:00      | 
+-----------+------------+---------------+
2 rows in set (0.00 sec)

#3

It depends on how you're storing the open days (I assume you're using a date value) but you could combine both the date and time in a single column as long as the data type for the DBMS supports it. This would eliminate having to join "open_hours" to "open_days".

这取决于您如何存储打开的日期(我假设您使用的是日期值)，但是只要DBMS支持数据类型，您可以将日期和时间合并到一个列中。这将消除不得不加入“open_hours”到“open_days”的需要。

#4

You're on the right path. The entities I see are:

你走对了路。我看到的实体是:

Restaurant Chain (eg TGI Friday's)
连锁餐厅(如周五的TGI)
Restaurant (specific one);
餐厅(特定的);
Opening Time (restaurant, day, open time, close time)
营业时间(餐厅、日、营业时间、关门时间)

Having an entity for day seems unnecessary.

白天有实体似乎是不必要的。

#5

From a data model design, it is not strictly necessary to have an ID column as the primary key in the tables for chain and branches: you may have to define a unique constraint for some other column (like for the chain name, for example), and that column may perfectly be the primary key. The ID column would be a surrogate key, and you may decide to have it or not. Some people consider it a good practice to always have such a primary key, but it is not strictly necessary. You may even find that in many cases it is not worth having it.

从数据模型设计,它并不是一定需要有一个ID列作为链表中的主键和分支:你可能需要定义一个独特的其他一些列的约束(比如链名称),这完全可能是主键列。ID列将是代理键，您可以决定是否拥有它。有些人认为总是有这样的主键是一种很好的做法，但并不是绝对必要的。你甚至会发现，在很多情况下，它不值得拥有。

#6

You could consider having the restaurant name, day-of-week and opening hours in a single table.
EG. Freds Diner/Monday/10.00/22.00/Tuesday/11.00/21.00/ etc. And before you send the Normalization Police around, please note that full integrity is maintained in this structure. It is unlikely that anyone is going to create a new day of the week in the foreseeable future.
Considerations on whether to use this structure relate to how easy it is to programmatically access/read/update the information. You could also include the restaurant address in the same row if required. Note. 'Address' is an an Attribute TYPE - not an attribute per se. It is a COMPOSITE attribute type as is DATE. NAME + Attribute Type = Attribute EG INVOICE + ADDRESS = Invoice Address and INVOICE + DATE = Invoice Date. Invoice Address and Invoice Date are both single composite attributes and follow the same rules. The main issue regarding the data structure is DATA INTEGRITY - normalization is simply a way to try to ensure the integrity of data.

你可以考虑在一张桌子上写上餐馆的名字、每周的日期和营业时间。如。Freds Diner/Monday/10.00/22.00/Tuesday/ Tuesday/11.00/21.00/等。在你派归正警察过来之前，请注意，这个结构保持了完整的完整性。在可预见的未来，任何人都不太可能创造一周中的新一天。是否使用此结构的考虑因素与以编程方式访问/读取/更新信息有多容易有关。如果需要，您还可以在同一行中包含餐馆地址。请注意。“Address”是一个属性类型，而不是属性本身。它是与日期一样的复合属性类型。名称+属性类型=属性如发票+地址=发票地址和发票+日期=发票日期。发票地址和发票日期都是单一的复合属性，并遵循相同的规则。关于数据结构的主要问题是数据完整性——标准化只是一种确保数据完整性的方法。

#1