I will assume that SQLite(或其他关系型数据库管理系统)是必需的。
EAVs
我曾经使用过EAV和通用数据模型,可以说这种数据模型非常混乱,长期使用很难处理。
假设您设计了一个包含三个表的数据模型:
entities、
attributes和_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
非常简单。您只需要插入一个新对象、一堆属性和它们之间的关系。例如,要插入具有 3 个属性的新实体,您需要执行 7 次插入(一个用于实体、三个用于属性,以及三个用于关系)。
当您想执行
UPDATE
时,您需要知道要更新的实体是什么,并通过连接实体和其属性之间的关系来更新所需的属性。
当您想执行
DELETE
操作时,您还需要知道要删除的实体是什么,删除其属性,删除实体和其属性之间的关系,然后再删除实体。
但是当您想执行
SELECT
操作时,情况变得很棘手(您需要编写非常复杂的查询),而且性能会大大下降。
想象一下一个数据模型来存储汽车实体及其属性,就像您的示例中一样(假设我们要存储品牌和型号)。一个查询所有记录的
SELECT
将是:
SELECT brand, model FROM cars;
如果您像示例中一样设计通用数据模型,查询所有存储的汽车的
SELECT
将非常困难,并涉及三个表的连接。查询性能将非常差。
此外,请考虑属性的定义。所有属性都存储为
TEXT
,这可能会成为一个问题。如果有人将“red”存储为价格,会怎么样?
索引是另一件您无法从中受益的事情(或者至少不如所需),随着存储的数据增长,它们非常必要。
正如您所说,作为开发人员的主要关注点是查询非常难编写、难以测试和难以维护(客户要支付多少钱才能购买您拥有的所有红色、1980年、Pontiac Firebirds?),并且在数据量增加时性能非常差。
使用EAVs的唯一优点是可以使用相同的模型存储几乎所有内容,但就像拥有一个装满东西的盒子,您想找到一个具体的、小的物品一样。
Also, to use an argument from authority, I will say that Tom Kyte argues strongly against generic data models:
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
Dynamic columns in database tables
On the other hand, as you mentioned, it's possible to generate tables dynamically by adding (and removing) columns when needed. For example, a basic
car table can be created with known attributes and then columns can be added dynamically when needed (such as the number of exhausts).
The disadvantage is that existing tables will need to have columns added, and possibly new indexes built.
正如您所说,这个模型在使用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)。如果您需要知道当类型为“红色”时属性37代表什么,您需要添加另一个表,将类型和属性与属性描述相关联。如果您发现其中一个实体需要更多属性,那么只需向实体表添加新列(attributeN+1,...)即可。在这种情况下,属性始终以TEXT形式存储(如EAVs),具有其缺点。但是,您可以使用索引,查询非常简单,该模型足够通用适用于您的情况,并且总的来说,我认为该模型的好处大于缺点。希望能对您有所帮助。
根据评论进行跟进:
使用伪泛型模型后,您的实体表将具有许多列。从文档(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行和每列一个小索引。