时间戳

首页 > 解决方案 > 在关系数据库中存储具有多个值的属性

mariadb - 在关系数据库中存储具有多个值的属性

问题描述

我通过以下方式将产品属性存储在 MariaDB 数据库的关系表中：

我有一个名为的主表，Products它提供有关产品的名称、描述和其他简单信息，还有另一个表，ProductAttributes具有以下结构：Id|ProductId|Attribute|Value其中Id是自动递增的主键，ProductId是对表中行的引用Products。

我可以通过这种方式将简单的属性值关系存储到产品中，例如产品的 、height、weight、length。我的问题开始，当一个产品的属性，即color可以有多个可能的值。

ProductAttributes存储多值属性时，我可以在表中添加多行，即：

1|yy|color|red
2|yy|color|blue

并且从这个模式中，我可以轻松地检索单个产品的属性，但是在尝试根据它们的属性比较两个产品时，我无法继续前进。

有没有其他方法可以将单个属性的多个值存储在关系数据库中以保持其可搜索性？

截至目前，要查找类似的属性产品，我正在执行类似的查询：

SELECT * FROM ProductAttributes base 
 INNER JOIN ProductAttributes compare ON compare.ProductId != base.ProductId
 WHERE base.Attribute = compare.Attribute
   AND base.Value = compare.Value 
   AND base.ProductId = 'x' 
 GROUP BY compare.ProductId

我的问题是，此查询将返回带有 ared和bluecolor 的产品，类似于带有blue颜色的产品。

顺便说一句，我不能将我的属性表更改为每列表示一个属性，因为我从一开始就不知道我会有多少属性，即使我知道，我有太多可能的属性和差异每个产品类别，以在传统表格中表示。

一个可能的陷阱是，我还想将缺少属性的产品相互比较。即，如果一个产品具有指定的长度属性，但另一个没有长度属性，它们仍然可能是相似的。现在，为了进行这种比较，在后台，我将我的属性表转换为一个简单的表，并在该表上执行以下查询：

SELECT b.ProductId as BaseProduct, s.ProductId as SimProduct 
  FROM tmp_transposed_product_attributes b 
 CROSS JOIN tmp_transposed_product_attributes s ON b.ProductId != s.ProductId 
 WHERE (b.attribute1 = s.attribute1 OR b.attribute1 IS NULL OR s.attribute1 IS NULL) 
   AND (b.attribute2 = s.attribute2 OR b.attribute2 IS NULL OR s.attribute2 IS NULL) ...

标签： mariadbentity-attribute-value

解决方案

如果我正确地跟踪产品比较，我喜欢使用EXISTS或NOT EXISTS帮助找到类似的东西，这也可能有助于避免转置数据。

例如，给定这个示例表数据：

MariaDB [test]> select * from productattributes;
+----+-----------+-----------+-------+
| id | productID | attribute | value |
+----+-----------+-----------+-------+
|  1 | yy        | height    | 5     |
|  2 | yy        | color     | red   |
|  3 | yy        | weight    | 10    |
|  4 | yy        | length    | 6     |
|  5 | yy        | color     | blue  |
|  6 | zz        | color     | white |
|  7 | zz        | height    | 5     |
|  8 | zz        | length    | 8     |

+----+-----------+-----------+-------+
8 rows in set (0.00 sec)

要查找两者之间的所有相似属性，但具有不同的值（删除相同的属性/值对），请使用NOT EXISTS对同一个表的查询，如下所示：

MariaDB [test]> SELECT * FROM `productattributes` pA
    ->  WHERE productID IN ('yy', 'zz')
    ->    AND NOT EXISTS (SELECT * FROM productattributes pB
    ->                     WHERE pA.attribute = pB.attribute
    ->                       AND pA.value = pB.value
    ->                       AND pA.productID != pB.productID)
    ->  ORDER BY productID, attribute;
+----+-----------+-----------+-------+
| id | productID | attribute | value |
+----+-----------+-----------+-------+
|  2 | yy        | color     | red   |
|  5 | yy        | color     | blue  |
|  4 | yy        | length    | 6     |
|  3 | yy        | weight    | 10    |
|  6 | zz        | color     | white |
|  8 | zz        | length    | 8     |
+----+-----------+-----------+-------+
6 rows in set (0.00 sec)

然后要查找两者之间相同的属性/值对，只需删除NOT查询的部分：

MariaDB [test]> SELECT * FROM `productattributes` pA
    ->  WHERE productID IN ('yy', 'zz')
    ->    AND EXISTS (SELECT * FROM productattributes pB
    ->                 WHERE pA.attribute = pB.attribute
    ->                   AND pA.value = pB.value
    ->                   AND pA.productID != pB.productID)
    ->  ORDER BY productID, attribute;
+----+-----------+-----------+-------+
| id | productID | attribute | value |
+----+-----------+-----------+-------+
|  1 | yy        | height    | 5     |
|  7 | zz        | height    | 5     |
+----+-----------+-----------+-------+
2 rows in set (0.00 sec)

这是没有命令行垃圾的查询：

SELECT * FROM `productattributes` pA
 WHERE productID IN ('yy', 'zz')
   AND NOT EXISTS (SELECT * FROM productattributes pB
                    WHERE pA.attribute = pB.attribute
                      AND pA.value = pB.value 
                      AND pA.productID != pB.productID)
 ORDER BY productID, attribute;

编辑：
为了涵盖一个属性在一个但不在另一个的情况，然后value可以删除查询的检查：

MariaDB [test]> SELECT * FROM `productattributes` pA
    ->  WHERE productID IN ('yy', 'zz')
    ->    AND NOT EXISTS (SELECT * FROM productattributes pB
    ->                     WHERE pA.attribute = pB.attribute
    ->                       AND pA.productID != pB.productID)
    ->  ORDER BY productID, attribute;
+----+-----------+-----------+-------+
| id | productID | attribute | value |
+----+-----------+-----------+-------+
|  3 | yy        | weight    | 10    |
+----+-----------+-----------+-------+
1 row in set (0.00 sec)

推荐阅读