SQL 中存储多级描述的最佳方法是什么？

Question

我需要一个新的视角来了解如何设计可靠且高效的 SQL 数据库来存储多级数据数组。

这个问题适用于很多情况，但我想出了这个例子：
有数百种产品。每个产品都有未定义数量的零件。每个部分都由多个元素组成。

所有产品都以相同的方式描述。所有零件都需要相同的字段来描述它们（例如：价格、重量、零件名称），所有零件的所有元素也具有统一的设计（例如：元素代码、制造商）。简单明了。
一个元素可能只与一部分相关，而每一部分只与一种产品相关。

我想出了三个表的想法：

Products:
--------------------------------------------
prod_id   prod_name   prod_price   prod_desc 
1            hoover          120      unused

next

Parts:
----------------------------------------------------
part_id  part_name  part_price  part_weight  prod_id
      3     engine          10           20        1

和finally

Elements:
---------------------------------------
el_id  el_code  el_manufacturer part_id
    1    BFG12               GE       3

现在，选择所需的产品，从 prod_id 相同的 PARTS 中选择所有产品，然后从 part_id 匹配的 ELEMENTS 中选择所有产品 - 经过多次查询后，您已经获得了所有数据。

我只是不确定这是否是正确的方法。
我还有另一个想法，没有 ELEMENTS 表。
这会减少查询，但我有点担心这可能是蹩脚和不好的做法。
PARTS 表中多了两个字段，而不是 ELEMENTS 表，因此它看起来像这样：
零件 ID、零件名称、零件价格、零件重量、产品 ID、零件_EL_代码、零件_EL_制造商
它们将是 text 类型，对于每个部分，有关元素的信息将存储为字符串，这样：

part_el_code         | code_of_element1; code_of_element2; code_of_element3

part_el_manufacturer | manuf_of_element1; manuf_of_element2; manuf_of_element3

然后我们需要的是从这些字段中爆炸（）数据，然后我们得到数组，很简单显示。 当然这并不完美并且有一些局限性，但是这个想法可以吗？

我提出第二个变体的原因是第三个表 - Elements - 最终会变得相当大。如果有 10,000 个产品，每个产品有 4 个零件，每个零件平均有 3 个元素 - 这意味着 Elements 表中必须有 120,000 行。老实说 - 我真的不知道这是否会导致性能问题。

我应该遵循第一个想法吗？或者也许有更好的方法来解决这个问题？

Answer 1

描述事物之间的关系正是关系数据库所做的事情。任何关于 SQL 的书都应该给出很多例子，我建议寻找这样的书来更好地理解这些问题，但我会快速回答你的问题。

对于包含 3 个表的简单解决方案，您的方向是正确的，但您不需要三个 select 语句。这就是 join 的用途。在您的示例中，以下 select 语句将为您提供特定产品的所有数据。

select * 
from product
join parts on parts.prod_id = product.prod_id
join elements on elements.part_id = part.parts_id
where product.prod_id = x

这很好，并且完全可以满足您的要求。

---

至于你的第二个想法，这真的很糟糕。您正在使用关系数据库，它允许您存储数据集并将其关联。将列表存储在字符串值中会忽略关系数据库设计良好的功能。

---

但是，有一种方法可以改进您的设计。我认为产品和零件或零件和元素可能并不总是具有一对一的关系。所以我建议如下。

首先从零件表中删除 prod_id，然后从元素表中删除part_id。

然后创建两个“连接表”。我们称它们为 prodparts 和 partselement。它们看起来像这样：

prodparts would contain two columns prod_id and part_id (and maybe a count?)
partselement would contain two columns part_id and el_id (and maybe a count?)

现在这些表中的每一行都将描述这种关系。但这是一个简洁的部分，多个产品可以使用相同的部分，多个部分可以使用相同的元素。

选择只是稍微复杂一些，如下所示：

select * 
from product
join prodparts on procparts.prod_id = product.prod_id
join parts on parts.part_id = prodparts.part_id
join partselement on partelement.part_id = parts.part_id
join elements on elements.el_id = partelement.parts_id
where product.prod_id = x

Answer 2

你的第一个方法似乎很可靠。第二种方法会使元素数据的维护变得困难。我在第一个设计中注意到的一件事是产品和零件都有“价格”。它们的含义是否有所不同，或者零件价格的总和是否等于产品的最终价格？如果是这样，我建议删除产品级别的价格字段并在零件级别使用 sum() 函数。

除此之外...干得好！