mysql - 与多个表之一相关的数据库列

Question

我觉得这是一个非常基本的问题，尽管我已经在数据库方面做了很多工作，但我仍然无法找到一个好的方法。在谷歌搜索时似乎也找不到任何东西。

假设我有一堆表，每个表都描述一种类型的项目。

例如:

Table candles:
id█  name  █length█diameter▌burntime▌
▀▀█▀▀▀▀▀▀▀▀█▀▀▀▀▀▀█▀▀▀▀▀▀▀▀█▀▀▀▀▀▀▀▀▌
1 █ candle1█54    █  6     █  180   ▌
69█ candle2█29    █  16    █  145   ▌
80█ candle3█100   █ 40     █  110   ▌


Table coasters:
id█  name  █thickness█diameter▌color   ▌
▀▀█▀▀▀▀▀▀▀▀█▀▀▀▀▀▀▀▀▀█▀▀▀▀▀▀▀▀█▀▀▀▀▀▀▀▀▌
1 █coaster1█5        █  20    █#ffffff ▌
6 █coaster2█8        █  41    █#ff0051 ▌
8 █coaster3█7        █ 38     █#002199 ▌


Table baskets:
id█  name  █size█
▀▀█▀▀▀▀▀▀▀▀█▀▀▀▀█
1 █basket1 █5   █
2 █basket2 █8   █
3 █basket3 █22  █

现在我有另一张 table 了。我们称之为购物车。该表中的每条记录都需要保存这些项目的数组。这是我不知道如何继续的地方。所以我需要多对多关系，这意味着我需要一个联结/数据透视表。但如何呢？

我应该为每种类型的项目准备一个连接表吗？具有用于购物车 ID 的外键和用于蜡烛/杯垫等的记录 ID 的外键。然后，在列出购物车的内容时，我需要检查每个连接表，每种类型的商品都有一个连接表。对我来说听起来不太干净。

或者我应该只有一个联结表，其中有一列用于表名称，一列用于记录 ID？看起来也很脏。

我想有更好的方法可以做到这一点，但我想不出。这应该如何完成？

Answer 1

解决此问题的一种可能方法是将您拥有的项目视为对象，然后考虑如何对它们进行关系建模。如果您将所有项目视为具有属性和值的对象，那么这是存储此信息的通用架构。

这通常被称为 O/R Mapping

一个简单的关系示例可能如下所示:

对象 - 定义您的所有项目

+----+----------+---------+
| Id |   Name   |  Type   |
+----+----------+---------+
|  1 | Candle1  | Candle  |
|  2 | Coaster1 | Coaster |
|  3 | Basket1  | Basket  |
+----+----------+---------+

ObjectProperty - 定义所有属性

+----+-----------+----------+
| Id |   Name    | DataType |
+----+-----------+----------+
|  1 | Length    | Float    |
|  2 | Diameter  | Float    |
|  3 | Burntime  | Float    |
|  4 | Thickness | Float    |
|  5 | Color     | String   |
|  6 | Size      | Int      |
+----+-----------+----------+

ObjectPropertyValue - 定义对象和属性之间的关系以及值

+----+----------+------------+----------+------------+-----------+-------------+
| Id | ObjectId | PropertyId | ValueInt | ValueFloat | ValueDate | ValueString |
+----+----------+------------+----------+------------+-----------+-------------+
|  1 |        1 |          1 | NULL     | 54.0       | NULL      | NULL        |
|  2 |        1 |          2 | NULL     | 29.0       | NULL      | NULL        |
|  3 |        3 |          6 | 5        | NULL       | NULL      | NULL        |
+----+----------+------------+----------+------------+-----------+-------------+

要检索单个对象的所有值，您可以运行如下查询:

SELECT          OP.Name,
                OP.DataType,
                OPV.ValueInt,
                OPV.ValueFloat,
                OPV.ValueDate,
                OPV.ValueString

FROM            ObjectProperty OP
INNER JOIN      ObjectPropertyValue OPV ON OP.Id = OPV.PropertyId

WHERE           OPV.ObjectId = 1

以这种格式存储信息有几个优点:

• 它是可扩展的，因此您不需要每次创建新对象时都需要一个新表类型出现
• 您可以根据需要拥有任意数量的属性对象
• 输入属性值意味着您仍然可以进行相关查询基于数据类型

即要搜索长度小于 50 的所有蜡烛:

SELECT      O.Id, 
            O.Name,
            OP.Name,
            OPV.ValueFloat 
FROM        Object O
INNER JOIN  ObjectPropertyValue OPV ON O.Id = OPV.ObjectId
INNER JOIN  OpjectProperty OP ON OPV.PropertyId = OP.Id

WHERE       OP.Name = 'Length'
AND         O.Type = 'Candle'
AND         OPV.ValueFloat <= 50.0;

但是，也有缺点:

• 如果没有动态 SQL，很难动态构建查询，因为您首先需要知道要在特定对象上查找哪种属性，然后选择要在 ObjectPropertyValue 中搜索的适当列
• 将对象压缩为通用结构有时会使非常简单的操作变得非常复杂。例如，比较在表结构中获取蜡烛与上面的查询有多么容易。

还有其他缺点，但是详细阅读该主题的一个好地方是 O/R Mapping and Relational Data

最后，解决这个问题的另一种解决方案是 NoSQL, an alternative mechanism to storing objects relationally. 的概念。

杰夫·阿特伍德还有一个 great article就这个主题而言。