应该在数据类型表中放置EAV值吗？ [英] Should I place EAV values in a datatype table?

问题描述

我正在为商店，产品，客户，供应商和仓库等实体设计一个数据库。数据库将用作不同实体商店中的在线交易系统。

在我的情况下，商店，客户，供应商和仓库的所有属性都可以定义为列，因为随着时间的推移，它们不会变化很大然而，产品具有无限量的属性，所以我想将这些数据放在一个EAV模型中。

任何人都可以指出，值是否应该放在自己的数据类型特定的表（例如attribute_values_int，或作为通用attribute_value表中的列）由于性能原因，Magento已经选择了数据类型特定的值表，请参见： http://blog.magestore.com/2012/03/23/magento-certificate-eav-model-concepts

谢谢。

解决方案

坦白说，最好的选择是不是EAV 使用 hstore 字段， XML 或 json 。

在PostgreSQL中，使用每个数据类型表没有性能优势。 NULL 值存储在紧凑的 NULL bitmap，所以无论你是否有一个元组，如$ code>（NULL，NULL，NULL，42，NULL，NULL） code>或只是（42）。

这也允许你添加 CHECK 约束强制一个字段必须不为 - NULL ，所以你不会得到不同类型的多个值。

演示：

  regress => CREATE TABLE eav_ugh（
 entity_id integer，
 int_value integer，
 numeric_value numeric，
 text_value text，
 timestamp_value timestamp with time zone，
 CONSTRAINT only_one_non_null CHECK 
（int_value IS NOT NULL AND numeric_value IS NULL AND text_value IS NULL AND timestamp_value IS NULL）OR 
（int_value IS NULL AND numeric_value IS NOT NULL AND text_value IS NULL AND timestamp_value IS NULL）OR 
 （int_value IS NULL AND numeric_value IS NULL AND text_value IS NOT NULL AND timestamp_value IS NULL）OR 
（int_value IS NULL AND numeric_value IS NULL AND text_value IS NULL AND timestamp_value IS NOT NULL）
）
 ）; 
 CREATE TABLE 
回归=>插入到eav_ugh（entity_id，numeric_value）中，从generate_series（1,5000）x中选择x，x; 
 INSERT 0 5000 
回归=>选择pg_relation_size（'eav_ugh'）; 
 pg_relation_size 
 ------------------ 
 229376 
（1行）
 
回归= > CREATE TABLE no_null_cols（entity_id integer，numeric_value numeric）; 
 CREATE TABLE 
回归=> insert into no_null_cols（entity_id，numeric_value）select x，x from generate_series（1,5000）x; 
 INSERT 0 5000 
回归=>选择pg_relation_size（'no_null_cols'）; 
 pg_relation_size 
 ------------------ 
 229376 
（1行）
 
回归= > SELECT sum（pg_column_size（eav_ugh））FROM eav_ugh; 
 sum 
 -------- 
 164997 
（1行）
 
回归=> SELECT sum（pg_column_size（no_null_cols））FROM no_null_cols; 
 sum 
 -------- 
 164997 
（1行）
  

在这种情况下，空位图根本不会添加任何空格，可能是由于对齐要求。

I am designing a database for entities like stores, products, customers, suppliers and warehouses. The database will be used as an online transactional system in different physical stores.

In my case all the attributes of stores, customers, suppliers and warehouses can be defined as columns because they won't change much over time. However the products have an unlimited amount of attributes so I would like to put this data in an EAV model.

Can anybody point out if the values should be put in their own datatype-specific table (such as attribute_values_int, or as columns in the generic attribute_value table? Because of performance reasons Magento has chosen datatype-specific value tables. See: http://blog.magestore.com/2012/03/23/magento-certificate-eav-model-concepts

Thanks.

解决方案

Frankly, the best option is "not EAV". Look into using hstore fields, XML, or json.

In PostgreSQL there is no performance advantage to using per-datatype tables. NULL values are stored in a compact NULL bitmap, so it makes very little difference whether you have a tuple like (NULL, NULL, NULL, 42, NULL, NULL) or just (42).

This also allows you to add CHECK constraint enforcing that exactly one field must be non-NULL, so you don't get multiple values of different types.

Demo:

regress=> CREATE TABLE eav_ugh (
    entity_id integer,
    int_value integer,
    numeric_value numeric,
    text_value text,
    timestamp_value timestamp with time zone,
    CONSTRAINT only_one_non_null CHECK (
            (int_value IS NOT NULL AND numeric_value IS NULL AND text_value IS NULL AND timestamp_value IS NULL) OR
            (int_value IS NULL AND numeric_value IS NOT NULL AND text_value IS NULL AND timestamp_value IS NULL) OR
            (int_value IS NULL AND numeric_value IS NULL AND text_value IS NOT NULL AND timestamp_value IS NULL) OR
            (int_value IS NULL AND numeric_value IS NULL AND text_value IS NULL AND timestamp_value IS NOT NULL)
    )
);
CREATE TABLE
regress=> insert into eav_ugh (entity_id, numeric_value) select x, x from generate_series(1,5000) x;
INSERT 0 5000
regress=> select pg_relation_size('eav_ugh');                                           
 pg_relation_size 
------------------
           229376
(1 row)

regress=> CREATE TABLE no_null_cols(entity_id integer, numeric_value numeric);
CREATE TABLE
regress=> insert into no_null_cols (entity_id, numeric_value) select x, x from generate_series(1,5000) x;
INSERT 0 5000
regress=> select pg_relation_size('no_null_cols');
 pg_relation_size 
------------------
           229376
(1 row)

regress=> SELECT sum(pg_column_size(eav_ugh)) FROM eav_ugh;
  sum   
--------
 164997
(1 row)

regress=> SELECT sum(pg_column_size(no_null_cols)) FROM no_null_cols;
  sum   
--------
 164997
(1 row)

In this case the null bitmap isn't adding any space at all, likely due to alignment requirements.

这篇关于应该在数据类型表中放置EAV值吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！