电子商务、类别中的产品和类别浏览

Question

我正在为客户构建一个 EC 网站，项目经理提出了一些奇怪的想法，而我正在努力实际实施他卖给客户的内容。

这是我的主要问题和系统设置方式的快速总结：产品位于类别内，类别可以是另一个类别的子类别。因此，该类别在网站的左侧栏上以树的形式呈现。

用户可以浏览任何类别，甚至是非“叶”类别，如果用户单击非叶类别，则应在 1 级类别上显示类似的列表（同样适用于 2 级类别）

big category 1
 category level ( 3 or 2 )
  product 1
  product 2
  product 3
 category level ( 3 or 2 ) 

：一些分页并在每页显示 5 个产品。另外，类别应该以与左侧菜单中出现的相同方式排序...我的数据库方案是这样的：

+-------------+    +-------------+
+ category    +    + product     +
+-------------+    +-------------+
+ category_id +    + product_id  +
+ parent_id   +    + category_id + 
+-------------+    +-------------+

我真的无法弄清楚应该如何编写 SQL 代码以确保产品按应有的顺序出现（例如订购产品和类别有菜单）。

另外，我还担心整个设置的性能，如果用户选择非“叶”类别，我将不得不搜索所有子类别并创建一个大类别（ id1, id2, id3 ），并且我根据经验知道IN 语句表现不佳。

如果有人遇到相同的设计/问题并提供一些如何制作的建议，我将不胜感激。

Answer 1

您可以使用物化路径设计。目录路径是具体化路径的示例。也就是说，一系列祖先值连接在一起，并用一些字符（“/”或“,”很常见）将它们分隔开。

因此，您可能有类别：

+---------------------------------------------+
| cat_id | Name            | cat_path | depth |
+---------------------------------------------+
|    1   | Electronics     | 1/       |   1   |
|    2   | Digital cameras | 1/2/     |   2   |
|    3   | SLR cameras     | 1/2/3/   |   3   |
|    4   | Audio           | 1/4/     |   2   |
|    5   | Speakers        | 1/4/5/   |   3   |
|    6   | Wall Satellites | 1/4/5/6/ |   4   |
|    7   | Computers       | 1/7/     |   2   |
+---------------------------------------------+

现在，如果您想要音频下的所有产品，您可以执行如下查询：

SELECT p.*, pc.*
FROM Products p JOIN Categories pc ON (p.cat_id = pc.cat_id)
JOIN Categories c ON (pc.cat_path LIKE c.cat_path||'%')
WHERE c.name = 'Audio';

例如，'1/4/5/6' LIKE '1/4/%' 是正确的，因此包括墙壁卫星。对于音频的任何其他子类别也是如此。

---

关于您关于菜单渲染的问题：我假设您希望渲染菜单：
- 所选类别的所有祖先
- 所选类别祖先的所有兄弟姐妹

因此，如果您选择“扬声器”，您会看到：

• 电子产品
  • 音频
    • 演讲者
  • 计算机
  • 数码相机

但是您不想要计算机或数码相机的后代（即扬声器的“表兄弟”）。

SELECT uncle.name, uncle.depth
FROM Categories chosen
JOIN Categories ancestor ON (chosen.cat_path LIKE ancestor.cat_path||'%')
JOIN Categories uncle ON (ancestor.depth = uncle.depth
  AND SUBSTRING(REVERSE(ancestor.cat_path), 3, 100) = SUBSTRING(REVERSE(uncle.cat_path), 3, 100))
WHERE chosen.name = 'Speakers'
ORDER BY uncle.depth, uncle.name;

我正在使用一个技巧来检测叔叔：在剥离最后一个元素后比较路径。为此，请反转字符串，然后删除 first 元素。这至少应该可以在 MySQL 和 MS SQL Server 中工作，但是 REVERSE() 不是标准的，并且可能无法移植到其他品牌的 RDBMS。

请注意，您可能应该允许 cat_path 中的每个元素使用多于一位的数字，在这种情况下，子字符串偏移量也应该增加。

Answer 2

从性能角度来看，这是一个糟糕的设计。如果客户不小心点击了最上面的类别，您将执行整个库存的查询。这可能会花费令人无法接受的时间。用网络术语来说，这意味着客户失去耐心，点击进入竞争对手的网站，然后再也不会访问您的网站。

当然，过早的优化是万恶之源，但避免做完全愚蠢的事情是个好主意。

我也会对树导航作为一种方法的整个想法提出异议。这有点像要求客户玩“猜猜我们如何盘点库存”的游戏。抛开其他事情不谈，在许多领域，一种产品可以属于多个类别，因此将它们放入层次结构中是一个任意的过程。至少您可能应该有一个支持将产品分配给多个叶类别的数据模型。 （这可能取决于您所销售商品的性质以及类别的粒度）。

如果你的老板坚持他们的方式，那么你仍然有一些选择来提高查询的性能。例如，您可能有一个表，其中包含由所有父类别连接的所有产品...

cat1 product1
cat1 product2
cat1 product3
cat1 product4
cat1 cat1.1 product1
cat1 cat1.1 product2
cat1 cat1.2 product3
cat1 cat1.2 product4
cat1 cat1.1 cat1.1.1 product1
cat1 cat1.1 cat1.1.2 product2
cat1 cat1.2 cat1.2.1 product3
cat1 cat1.2 cat1.2.2 product4

您必须通过触发器或物化视图或通过某种其他机制（取决于您的数据库风格提供的内容）来维护它。但与不必为每个客户查询重新组装产品层次结构所带来的性能优势相比，维护它的开销可以忽略不计。此外，您的库存不太可能有那么大的波动。