mysql - Should I place EAV values in a datatype table? -

i designing database entities stores, products, customers, suppliers , warehouses. database used online transactional system in different physical stores.

in case attributes of stores, customers, suppliers , warehouses can defined columns because won't change on time. products have unlimited amount of attributes put data in eav model.

can point out if values should put in own datatype-specific table (such attribute_values_int, or columns in 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, best option "not eav". using hstore fields, xml, or json.

in postgresql there no performance advantage using per-datatype tables. null values stored in compact null bitmap, makes little difference whether have tuple (null, null, null, 42, null, null) or (42).

this allows add check constraint enforcing 1 field must non-null, don't 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 time zone,     constraint only_one_non_null check (             (int_value not null , numeric_value null , text_value null , timestamp_value null) or             (int_value null , numeric_value not null , text_value null , timestamp_value null) or             (int_value null , numeric_value null , text_value not null , timestamp_value null) or             (int_value null , numeric_value null , text_value null , timestamp_value not null)     ) ); create table regress=> insert eav_ugh (entity_id, numeric_value) select x, x 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 no_null_cols (entity_id, numeric_value) select x, x 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)) eav_ugh;   sum    --------  164997 (1 row)  regress=> select sum(pg_column_size(no_null_cols)) no_null_cols;   sum    --------  164997 (1 row) 

in case null bitmap isn't adding space @ all, due alignment requirements.