database - 数据库表中的动态列与 EAV

Question

如果我的应用程序需要能够根据用户输入更改 db 架构，我正在尝试决定该走哪条路。

例如，如果我有一个包含汽车属性(如年份、型号、门数量等)的“汽车”对象，我该如何将它存储在数据库中，以便用户能够添加新属性？

我读过 EAV 表，它们似乎适合这件事，但问题是当我尝试获取由一组属性过滤的汽车列表时，查询会变得非常复杂。

我可以动态生成表格吗？我看到 Sqlite 支持 ADD COLUMN ，但是当表达到多条记录时，速度有多快？看起来没有办法删除一列。我必须创建一个没有要删除的列的新表，然后从旧表中复制数据。这在大 table 上肯定很慢:(

Answer 1

我将假设 SQLite(或其他关系 DBMS)是必需的。

EAV

我曾使用过 EAV 和通用数据模型，我可以说，从长远来看，数据模型非常困惑且难以使用。

假设您设计了一个包含三个表的数据模型:实体、属性和_entities_attributes_:

CREATE TABLE entities
(entity_id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE attributes 
(attribute_id INTEGER PRIMARY KEY, name TEXT, type TEXT);

CREATE TABLE entity_attributes 
(entity_id INTEGER, attribute_id INTEGER, value TEXT, 
PRIMARY KEY(entity_id, attribute_id));

在此模型中，实体表将保存您的汽车，属性表将保存您可以关联到汽车的属性(品牌、型号、颜色等)及其类型(文本、数字、日期等)。 )，并且 _entity_attributes_ 将保存给定实体的属性值(例如“红色”)。

考虑到使用此模型，您可以存储任意数量的实体，它们可以是汽车、房屋、计算机、狗或其他任何东西(好吧，也许您需要在实体上添加一个新字段，但这对于示例来说已经足够了)。
INSERT s 非常简单。您只需要插入一个新对象、一堆属性及其关系。例如，要插入一个具有 3 个属性的新实体，您需要执行 7 次插入(一个用于实体，另外三个用于属性，另外三个用于关系。

当您想执行 UPDATE 时，您将需要知道要更新的实体是什么，并更新所需的属性，并连接实体与其属性之间的关系。

当您想执行 DELETE 时，您还需要知道要删除的实体是什么，删除其属性，删除实体与其属性之间的关系，然后删除实体。

但是当你想执行一个 SELECT事情变得很糟糕(您需要编写非常困难的查询)并且性能下降得可怕。

想象一个数据模型来存储汽车实体及其属性，如您的示例(假设我们要存储品牌和型号)。一个 SELECT查询您的所有记录将是

SELECT brand, model FROM cars;

如果您像示例中那样设计通用数据模型， SELECT查询所有存储的汽车将非常难以编写，并且将涉及 3 个表连接。查询将执行得非常糟糕。

另外，请考虑您的属性的定义。您的所有属性都存储为 TEXT ，这可能是一个问题。如果有人犯了错误并将“红色”存储为价格怎么办？

索引是您无法从中受益的另一件事(或者至少没有您想要的那么多)，并且随着存储的数据的增长，它们非常必要。

正如您所说，作为开发人员的主要担忧是查询真的很难编写、难以测试和难以维护(客户需要支付多少钱才能购买您拥有的所有红色、1980 年的 Pontiac Firebirds？) ，并且在数据量增加时性能会很差。

使用 EAV 的唯一优势是您可以用相同的模型存储几乎所有东西，但这就像在装满东西的盒子里找到一个具体的小物品。

另外，为了使用权威的论点，我会说 Tom Kyte 强烈反对通用数据模型:
http://tkyte.blogspot.com.es/2009/01/this-should-be-fun-to-watch.html
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10678084117056

数据库表中的动态列

另一方面，正如您所说，您可以动态生成表，在需要时添加(和删除)列。在这种情况下，例如，您可以创建一个具有您知道将使用的基本属性的汽车表，然后在需要时动态添加列(例如排气数量)。

缺点是您需要向现有表添加列并(可能)构建新索引。

正如您所说，这个模型在使用 SQLite 时还有另一个问题，因为没有直接的方法来删除列，您需要按照 http://www.sqlite.org/faq.html#q11 中的说明执行此操作。

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b);
INSERT INTO t1 SELECT a,b FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;

无论如何，我真的不认为您需要删除列(或者至少这是一种非常罕见的情况)。也许有人将门的数量添加为一列，并存储具有此属性的汽车。在删除列之前，您需要确保您的任何汽车都具有此属性以防止丢失数据。但这当然取决于您的具体情况。

此解决方案的另一个缺点是，您需要为要存储的每个实体准备一张表(一张表用于存储汽车，另一张表用于存储房屋，等等……)。

另一种选择(伪通用模型)

第三种选择可能是使用伪泛型模型，表中包含用于存储实体的 id、名称和类型的列，以及给定(足够)数量的泛型列来存储实体的属性。

假设您创建了一个这样的表:

CREATE TABLE entities
(entity_id INTEGER PRIMARY KEY,
 name TEXT,
 type TEXT,
 attribute1 TEXT,
 attribute1 TEXT,
 ...
 attributeN TEXT
 );

在此表中，您可以存储任何实体(汽车、房屋、狗)，因为您有一个类型字段，并且您可以根据需要为每个实体存储任意数量的属性(在本例中为 N)。

如果您需要知道当 type 为“red”时 attribute37 代表什么，您需要添加另一个表，将类型和属性与属性描述相关联。

如果您发现您的实体之一需要更多属性怎么办？然后只需将新列添加到实体表(attributeN+1, ...)。

在这种情况下，属性总是存储为 TEXT(如在 EAV 中)，这是有缺点的。

但是你可以使用索引，查询真的很简单，模型对于你的情况来说足够通用，总的来说，我认为这个模型的好处大于缺点。

希望能帮助到你。

从评论中跟进:

使用伪通用模型，您的实体表将有很多列。从文档 ( https://www.sqlite.org/limits.html ) 中，SQLITE_MAX_COLUMN 的默认设置是 2000。我使用过具有超过 100 列的 SQLite 表并具有出色的性能，因此 40 列对于 SQLite 来说应该不是什么大问题。

正如您所说，对于您的大多数记录，您的大部分列都是空的，并且您需要索引所有列以提高性能，但您可以使用部分索引( https://www.sqlite.org/partialindex.html )。这样，即使行数很多，您的索引也会很小，并且每个索引的选择性也会很大。

如果你实现一个只有两个表的 EAV，表之间的连接数量会比我的例子中少，但是查询仍然很难编写和维护，你需要做几个(外部)连接来提取数据，当您存储大量数据时，即使索引很大，也会降低性能。例如，假设您想获取汽车的品牌、型号和颜色。您的 SELECT看起来像这样:

SELECT e.name, a1.value brand, a2.value model, a3.value color
FROM entities e
LEFT JOIN entity_attributes a1 ON (e.entity_id = a1.entity_id and a1.attribute_id = 'brand')
LEFT JOIN entity_attributes a2 ON (e.entity_id = a2.entity_id and a2.attribute_id = 'model')
LEFT JOIN entity_attributes a3 ON (e.entity_id = a3.entity_id and a3.attribute_id = 'color');

如您所见，对于要查询(或过滤)的每个属性，您都需要一个(左)外部联接。使用伪通用模型，查询将如下所示:

SELECT name, attribute1 brand, attribute7 model, attribute35 color
FROM entities;

此外，请考虑您的 _entity_attributes_ 表的潜在大小。如果每个实体可能有 40 个属性，那么假设每个实体都有 20 个非空属性。如果您有 10,000 个实体，您的 _entity_attributes_ 表将有 200,000 行，您将使用一个巨大的索引来查询它。使用伪通用模型，您将拥有 10,000 行和每列一个小索引。