如何有效地在多对多关系中查找频率

Question

我正在构建一个数据库来存储一个人订购某个商品的次数。这是一种多对多的关系，但我想知道如何有效地存储客户订购某一商品的次数。我唯一的选择是计数和按语句分组，还是可以重新排列数据库？

我的桌子现在是这样的：

客户表：

客户名称

客户ID
1	Alice
2	Bob
3	Eve

如果这是关于您所拥有的细节。你可以随时引入另一个字段来保存购买物品的数量。如：

ALTER TABLE item_bought ADD COLUMN Count INTEGER NOT NULL;

并使用查询更新该表：

INSERT INTO item_bought (CustomerId, ItemId, Count)
VALUES (@var_customer_id, @var_item_id, @var_count)
ON DUPLICATE KEY UPDATE -- assuming CustomerId, ItemId is unique key
Count := Count + @var_count
;

并选择如下：

SELECT CustomerId, ItemId, Count
FROM item_bought
;

您的表格设计很好，如果您还没有添加更多信息，那么您可能想在items_buyed表格中添加更多信息(例如购买日期(。

为了知道客户#1购买了多少次物品，您需要

select i.item_id, count(ib.item_id)
from item i
left join items_bought ib on ib.item_id = i.item_id and ib.customer_id = 1
group by i.item_id
order by i.item_id;

你想要一个商品的索引。我会尝试以下两个，看看DBMS正在使用哪个，然后删除另一个：

create index idx1 on items_bought (item_id, customer_id);
create index idx2 on items_bought (customer_id, item_id);

不过，如果有一个已经包含所需数据的表，则会更方便。这是一种数据仓库方法。你可以添加一个表格

dwh_customer_item (customer_id, item_id, amount)

其中主密钥在CCD_ 1上。然后添加一个触发器，每当项目添加到数据库时，该触发器就会添加行，添加这些行：

insert into dwh_customer_item (customer_id, item_id, amount)
select c.customer_id, new.item_id, 0 as amount from customer c;

以及一个触发器，每当客户被添加到数据库时，就会添加行，添加这些行：

insert into dwh_customer_item (customer_id, item_id, amount)
select new.customer_id, i.item_id, 0 as amount from item i;

最后，一个增加每次插入商品金额的触发器_购买添加这些行：

update dwh_customer_item
set amount = amount + 1
where customer_id = new.customer_id
and item_id = new.item_id;

有了这样的概念，您甚至可以在不丢失总计数的情况下从items_buy中删除旧行。

不过，如果你想将所有这些添加到你的数据库中，这是你的选择。原始表很好，查询(提供适当的索引(已经很快了。