通过外键冗余以允许更简单的常见查询?

Question

我很想将外键添加到不需要它的表中，只是为了简化常见查询。会不会有那么坏？

下面是一个简化的示例来说明我正在处理的内容。

CREATE TABLE group
(
group_id INT AUTO_INCREMENT NOT NULL,
group VARCHAR(32) NOT NULL,
PRIMARY KEY (group_id),
)
CREATE TABLE item
(
item_id INT AUTO_INCREMENT NOT NULL,
item VARCHAR(32) NOT NULL,
PRIMARY KEY (item_id),
FOREIGN KEY (group_id) REFERENCES group(group_id)
)
CREATE TABLE order
(
order_id INT AUTO_INCREMENT NOT NULL,
order VARCHAR(32) NOT NULL,
PRIMARY KEY (order_id),
FOREIGN KEY (item_id) REFERENCES item(item_id)
-- FOREIGN KEY (group_id) REFERENCES item(group_id) <- not needed,  but...
)

从逻辑角度来看，只需要这种结构，但我知道查询订单表有两种常见的模式：

1. 通过item_id
2. 按group_id

在第二种情况下，查询应如下所示：

with merged as
(
SELECT *
FROM item JOIN group
ON item.group_id = group.group_id
)
SELECT *
FROM order JOIN merged
ON order.item_id = merged.item_id
WHERE merged.group = 'my_group'

如果我按顺序添加group_id作为外键，我将添加一些冗余(浪费一些空间(，但我可以放弃用户加入两次的需要。

此外，我只有 80% 的把握 db 优化器可以确保查询具有相同的性能(从技术上讲，它不能具有相同的性能，但它应该具有足够相似的性能(。

Answer 1

首先，group和order是SQL中的关键字。 这使得它们成为表名的非常糟糕的候选者。 我以复数形式制作表名的一个原因是因为它避免了与大多数关键字的冲突。

其次，使用外键关系来描述数据的结构。 您可以添加其他索引以提高性能。

第三，您需要声明列以在约束中使用它们。

你似乎想要：

CREATE TABLE groups (
group_id INT AUTO_INCREMENT NOT NULL,
group VARCHAR(32) NOT NULL,
PRIMARY KEY (group_id)
);
CREATE TABLE items (
item_id INT AUTO_INCREMENT NOT NULL,
item VARCHAR(32) NOT NULL,
group_id int,
PRIMARY KEY (item_id),
FOREIGN KEY (group_id) REFERENCES group(group_id)
)
CREATE TABLE orders (
order_id INT AUTO_INCREMENT NOT NULL,
order VARCHAR(32) NOT NULL,
item_id INT,
PRIMARY KEY (order_id),
FOREIGN KEY (item_id) REFERENCES item (item_id)
);

物料在组中，订单具有物料。 您没有描述任何需要在orders中拥有group_id。 您可以通过item_id查找它。 不要存储冗余信息。

当然，如果orders可以独立于itemgroups，那么额外的group_id列将是有意义的。