当需要太多列时，如何设计数据库？

Question

我有一张名为“汽车”的桌子，但每辆车都有数百种属性，它们随着时间的推移而不断增加(马力、扭矩、a/c、电动窗户等)。我的表将每个属性作为列。当我有数千行和数百列时，这是正确的方法吗？此外，我还为每个属性设置了一个列，以便于高级搜索/筛选。

使用MySQL数据库。

谢谢

Answer 1

这是一个有趣的问题，IMHO，答案可能取决于您的具体数据模型和实现。在这种情况下，最重要的因素是数据密度。

平均而言，每一行的实际填充量是多少？

• 如果您的大部分字段总是存在的，那么数据作用域分区可能是最好的选择。
• 如果您的大部分字段是空的，那么元数据-like结构(如@JayC建议的)可能更有吸引力。

让我们使用您提到的情况，并做一些模拟。

在第一种情况下，作用域分区，其思想是实现基于范围或使用的分区。作为按用法进行分区的示例，让我们假设检索最多的字段是Model、年份、Maker和Color。这些字段可以构成您的主汽车表，即ID字段的所有者，它将专门标识车辆。现在让我们假设引擎，马力，扭矩和汽缸也经常被用来搜索，但没有那么频繁。它们可能存在于辅助表CAR_INFO_1上，该表通过CAR_ID字段(外键)的存在而绑定到第一个表。继续创建所需的多个分区。

优点:查询更简单。如果您执行联合查询(例如，在视图中)，则可以合并有关车辆的所有信息。

缺点:维护。每个新字段都必须在模型本身中实现，并且需要更新的数据模型来定位您需要的字段实际存储的位置(或者将其抽象到视图中)。

元数据格式要优雅得多，但需要更多的数据库引擎。查看@JayC和@Nitzan Shaked的答案以获得详细信息。

优点:数据密度100%。您永远不会有空的数据值。另外，维护-通过将新属性作为行添加到元数据标识符表中来创建一个新属性。数据结构也不那么复杂。

缺点:复杂的查询，以及更复杂的执行计划。比方说，你需要的是2010年生产的所有蓝色福特汽车。在第一种情况下，这将是非常微不足道的：

SELECT * FROM CAR WHERE Model='Ford' AND Year='2010' AND Color='Blue'

现在，对元数据结构模型的相同查询：

假设这两个表的存在，

CAR_METADATA_TYPE
ID  DESC
1   'Model'
2   'Year'
3   'Color'

和

CAR_METADATA [CAR_ID], [METADATA_TYPE_ID], [VALUE]

查询本身需要这样的内容：

SELECT * FROM CAR, CAR_METADATA [MP1], CAR_METADATA [MP2], CAR_METADATA [MP3]
WHERE MP1.CAR_ID = CAR.ID AND MP1.METADATA_TYPE_ID = 1 AND MP1.Value='Ford'
AND MP2.CAR_ID = CAR.ID AND MP2.METADATA_TYPE_ID = 2 AND MP2.Value='2010'
AND MP3.CAR_ID = CAR.ID AND MP3.METADATA_TYPE_ID = 3 AND MP3.Value='Blue'

所以一切都取决于你的需要。但考虑到你的情况，我的建议是元数据格式。

(但是先做一个模型清理--没有重复的字段，在自己的表上有1:N的数据，而不是像Color1、Color2、Color3之类的内联字段;)

Answer 2

那么，我想最明显的问题是:为什么不拥有一张桌子car_attrs(汽车，attr，value)？每个属性都是一行。大多数查询都可以重写以使用此表单。

Answer 3

如果所有功能都是关于特性的，那么创建一个features表，将您的所有功能作为行列出，并为它们提供某种自动标识，并创建一个car_features，该表包含cars表和features表的外键，该表将汽车与功能相关联，可能还包括与该关系相关的任何值(一个乘客电动座椅，等等)。