EAV模型 Entity-Attribute-Value

EAV模型代表Entity-Attribute-Value,最早用于医学用途，医生在就诊时需要记录很多病人的参数，如体温，年龄，过敏药等情况，而这些参数并不是每个病人都需要记录的。

由于商品的多样性，用EAV表来描述商品的各种属性也很合适。老牌电子商务应用oscommerce的表设计(为了简洁，我将商品属性名和属性值的关系表略去)：

 

-- 商品表

CREATE TABLE `products` (

`      id` int(11) NOT NULL auto_increment,

`      products_name` varchar(50) default NULL,

PRIMARY KEY (`id`)

);

-- 商品属性表

CREATE TABLE `products_attributes` (

`id` int(11) NOT NULL auto_increment,

`products_id` int(11) NOT NULL default '0',

`attribute_name` varchar(50) default NULL,

PRIMARY KEY (`id`),

KEY `products_id_attribute_name` (`products_id`,`attribute_name`)

);

-- 属性值

CREATE TABLE `attribute_values` (

`attribute_id` int(11) NOT NULL default '0',

`attribute_value` varchar(100) default NULL,

UNIQUE KEY `attribute_id` (`attribute_id`,`attribute_value`)

);

 

EAV表模型带来了数据的灵活性，是的增加对象的属性不需要用增加数据库的字段，有很高的灵活性。但是EAV表也有较大的性能问题。通常，EAV表带来的一个问题是当查找多个字段时，需要进行关联查询join,这样的查询效率比较低。为了提高查询效率，我们可以对商品属性表进行矩阵转积处理(pivoting),

 

"SELECT items.item_name, ia.attribute_name, av.attribute_value

FROM attribute_values AS av JOIN item_attributes AS ia ON (ia.id = av.attribute_id)

JOIN items AS items ON (items.id = ia.item_id); ";

 

 

一种方式是在pap代码中读出后存入memcache, 当修改attributes表后php触发更新memcache或用cron定期更新;另一种方法是将关联信息组成一张大的临时表，或者view(mysql 5), 利于warehouse的查询，数据的更新可以用数据库的触发器触发更新。由于大量数据在php中进行处理带来了DB的额外IO和服务器性能问题，比较建议用后一种方式更新。

著名的ecommerce软件magento就采用了EAV表作为核心架构，下面看一下通常的表设计：

 

CREATE TABLE field_names (

fid INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

field_name VARCHAR(50) NOT NULL DEFAULT '',

field_type ENUM('VARCHAR', 'INTEGER', 'DOUBLE',

'DATE', 'TEXT') NOT NULL DEFAULT 'VARCHAR',

UNIQUE KEY (field_name)

);

 

CREATE TABLE varchar_values (

vid INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

value VARCHAR(255) NOT NULL DEFAULT '',

UNIQUE KEY (value)

);

CREATE TABLE integer_values (

vid INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

value INT(11) NOT NULL DEFAULT 0,

UNIQUE KEY (value)

);

CREATE TABLE double_values (

vid INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

value DOUBLE NOT NULL DEFAULT 0,

UNIQUE KEY (value)

);

CREATE TABLE date_values (

vid INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

value DATE NOT NULL DEFAULT '0000-00-00',

UNIQUE KEY (value)

);

CREATE TABLE text_values (

vid INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

value TEXT NOT NULL DEFAULT '',

UNIQUE KEY (value(100))

);

 

可以定义一些mysql函数，方便数据类型到具体表的转换

CREATE FUNCTION `value_display` (

`type` enum('NUMBER', 'ENUM', 'DATE', 'TIME', 'TEXT'), `value` INT, `option` VARCHAR(255), `text` TEXT, `precision` INT, `date_format` VARCHAR(50)) RETURNS VARCHAR(255) CHARACTER SET latin1 NO SQL

BEGIN

  CASE type

    WHEN 'NUMBER' THEN RETURN `value` / POW(10, `precision`);

    WHEN 'ENUM' THEN RETURN `option`;

    WHEN 'DATE' THEN RETURN DATE_FORMAT(FROM_DAYS(`value`), `date_format`);

    WHEN 'TIME' THEN RETURN FROM_UNIXTIME(`value`, `date_format`);

    WHEN 'TEXT' THEN RETURN `text`;

    ELSE RETURN NULL;

  END CASE;

  RETURN NULL;

END;

当使用EAV表模型时，InnoDB比MYISAM的性能要好不少。

 

使用EAV模型
现在，在Magento系统注册一个新的用户，看看实例数据如何存放在数据库
mysql> select * from customer_entity            ; 
+-----------+----------------+------------------+------------+----------------------------------+ 
| entity_id | entity_type_id | attribute_set_id | website_id | email      | 
+-----------+----------------+------------------+------------+---------------------------------+ 
|         1 |              1 |                0       |                1 | koda.guo@gmail.com
+-----------+----------------+------------------+------------+----------------------------------+ 
 
mysql> select * from customer_entity_varchar    ; 
+----------+----------------+--------------+-----------+----------------------------------------------+ 
| value_id | entity_type_id | attribute_id | entity_id | value                                     | 
+----------+----------------+--------------+-----------+----------------------------------------------+ 
|        1 |              1 |                  5 |         1 |      Koda                                      | 
|        2 |              1 |                  7 |         1 |      Guo                                        | 
|        4 |              1 |                  3 |         1 |     Default Store View                      | 
|        5 |              1 |                 12 |         1 |  2256e441b74ab3454a41c821f5de1e9d:9s | 
+----------+----------------+--------------+-----------+----------------------------------------------+ 
mysql> select * from customer_entity            ;
+-----------+----------------+------------------+------------+------------------------------+
| entity_id | entity_type_id | attribute_set_id | website_id | email                 |
+-----------+----------------+------------------+------------+------------------------------+
|         1 |                  1 |                0 |                1     | koda.guo@gmail.com |
+-----------+----------------+------------------+------------+------------------------------+

mysql> select * from customer_entity_varchar    ;
+----------+----------------+--------------+-----------+----------------------------------------------+
| value_id | entity_type_id | attribute_id | entity_id | value                                      |
+----------+----------------+--------------+-----------+----------------------------------------------+
|        1 |              1 |            5 |                  1 |    Koda                                      |
|        2 |              1 |            7 |                  1 |     Guo                                       |
|        4 |              1 |            3 |                  1 |     Default Store View                   |
|        5 |              1 |           12 |                 1 |   2256e441b74ab3454a41c821f5de1e9d:9s |
+----------+----------------+--------------+-----------+-------------------------------------+
从上表看到customer_entity 和customer_entity_varchar用来存放相应属性的实际输入值。如：
Koda, Guo分别属性编号5,7即firstname和lastname实际值
和customer实体定义相对应的实例存放的相关表包括： Java代码

customer_entity            customer_entity_datetime   customer_entity_decimal    customer_entity_int

customer_entity_text       customer_entity_varchar    customer_entity          customer_entity_datetime

customer_entity_decimal  customer_entity_int      customer_entity_text     customer_entity_varchar