<? echo

Project Statuses

Mental Fusion - 85%
FusionBB - 2%
This webpage is built with W3C compliant HTML5.
This webpage is built with W3C compliant CSS3.
Download: Fast, Fun, Awesome
Opera, the fastest and most secure web browser

Example Information

The EAV Database Model

Posted: March 2, 2012 11:40 AM CST by Michael
I've been doing a bit of research lately into different design patterns and models for both PHP and MySQL. One that I came across for MySQL that I was unfamiliar with was the EAV model. I didn't have a firm grasp of the EAV model, so I read up a bit on it and can now share what I've learned with you.

What is the EAV model? EAV stands for entity-attribute-value. The idea behind the EAV model is for every entity you have (entities can be products, companies, people, anything), you have a set of attributes for those entities. The attributes are all stored in the attributes table (attributes can be name, title, address, city, color, size, weight, etc). Now, each entity has to be given a specific type id so that you know what kind of entity it is (a product or a person for example) and that type id is used to link the entities to the attributes. So you might have a starting structure that looks something like this for your entity table:

mysql> DESC eav_entities;
mysql> desc eav_entities;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| entity_id | int(11) | NO | PRI | NULL | auto_increment |
| entity_type | varchar(255) | YES | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

Pretty simple, right? There's nothing in the entities table but the auto-incrementing entity_id (which will be used to link to our attributes table) and entity_type.

So now that our entities table is set up, let's see what a base structure for an attribute table should look like:

mysql> DESC eav_attributes;
+----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+----------------+
| attribute_id | int(11) | NO | PRI | NULL | auto_increment |
| entity_id | int(11) | YES | | NULL | |
| attribute_name | varchar(30) | YES | | NULL | |
| attribute_desc | varchar(255) | YES | | NULL | |
+----------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

Now, in this table we have our auto-incrementing attribute_id which will be used to link to our value tables, entity_id which links our eav_attributes, attribute_name which houses the name of the attribute we're working with, and eav_entities tables, and the attribute_desc column where we can provide a short description about the attribute or its use. Note that the length limitations I've used here are arbitrary.

With our eav_entities and eav_attributes tables set up, all that's left is configuring our value tables. First, let's establish what type of entity we want to work with. The beauty of this model is that it really doesn't matter what type of data we're working with as the value tables can be made to reflect whatever sort of information you want to store. For the purposes of this example, let's work with a product for an eCommerce store.

First, we need to add an entry to our eav_entities table:

mysql> INSERT INTO eav_entities SET entity_type = 'product';
Query OK, 1 row affected (0.00 sec)

Now we should add an attribute entry that will be used to link our entity to our attributes and our attributes to our values:

mysql> INSERT INTO eav_attributes SET entity_id = '1', attribute_name = 'SKU', attribute_desc = '12345';
Query OK, 1 row affected (0.00 sec)

We can now see the information we've added to our two tables with a simple query (for more information on MySQL Joins, please refer to my article, "The MySQL JOIN Statement"):

mysql> SELECT attribute_id, entity_type, attribute_name, attribute_desc FROM eav_attributes RIGHT JOIN eav_entities ON eav_entities.entity_id = eav_attributes.entity_id;
+--------------+-------------+----------------+----------------+
| attribute_id | entity_type | attribute_name | attribute_desc |
+--------------+-------------+----------------+----------------+
| 1 | product | SKU | 12345 |
+--------------+-------------+----------------+----------------+
1 row in set (0.00 sec)

Here, I've opted to use the attribute_name column as the label rather than the value, and I've put the value of SKU in the attribute_desc column. This is another arbitrary choice and doesn't impact the flow or function or our EAV structure.

Let's add some value tables now. What should a product have? Name, description, price, size, and color all sound like good things to have for a product. So we'll create a value table for each of these 5 items:

mysql> desc eav_product_name_values;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| attribute_id | int(11) | YES | | NULL | |
| value | varchar(255) | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc eav_product_description_values;
+--------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+-------+
| attribute_id | int(11) | YES | | NULL | |
| value | text | YES | | NULL | |
+--------------+---------+------+-----+---------+-------+
2 rows in set (0.02 sec)

mysql> desc eav_product_price_values;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| attribute_id | int(11) | YES | | NULL | |
| value | decimal(7,2) | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc eav_product_size_values;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| attribute_id | int(11) | YES | | NULL | |
| value | varchar(255) | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc eav_product_color_values;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| attribute_id | int(11) | YES | | NULL | |
| value | varchar(255) | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Phew. As you can see, each table is named for its purpose. It's a good idea for your table names to reflect the entity that they are for; otherwise, your database is going to become a maintenance nightmare. Further, each value column has a different type associated with it (where appropriate) to represent the data it will be storing.

All that's left now is adding in some sample data that we can pull for our test:

mysql> insert into eav_product_name_values set attribute_id = '1', value = 'Test Product 12345';
Query OK, 1 row affected (0.00 sec)

mysql> insert into eav_product_description_values set attribute_id = '1', value = 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donec molestie urna nec tortor ultrices id viverra arcu blandit. Etiam iaculis leo at ligula semper a tincidunt est ornare. Vestibulum feugiat bibendum dapibus. Morbi eget scelerisque mauris. Sed auctor ante at tortor egestas vitae vestibulum dolor vehicula. Ut ultricies metus dolor, lobortis hendrerit nunc. Aliquam egestas ultricies sodales. Phasellus a ultricies mauris. Aenean placerat risus eros.';
Query OK, 1 row affected (0.00 sec)

mysql> insert into eav_product_price_values set attribute_id = '1', value = '5.95';
Query OK, 1 row affected (0.00 sec)

Notice that I didn't add any color or size data. The reason for this is to hopefully show the flexibility of the EAV model. So let's retrieve our data again and see what it looks like. We could do this with several separate queries, or we can use just one. I'm just going to use just one because it's a bit easier to work with and only creates one query to the server as opposed to many:

mysql> SELECT a.attribute_id, e.entity_type, a.attribute_name, a.attribute_desc,
-> nv.value as product_name, dv.value as product_description,
-> pv.value as product_price, sv.value as product_size,
-> cv.value as product_color
-> FROM eav_attributes as a
-> RIGHT JOIN eav_entities as e ON e.entity_id = a.entity_id
-> LEFT JOIN eav_product_name_values as nv ON nv.attribute_id = a.attribute_id
-> LEFT JOIN eav_product_description_values as dv ON dv.attribute_id = a.attribute_id
-> LEFT JOIN eav_product_price_values as pv ON pv.attribute_id = a.attribute_id
-> LEFT JOIN eav_product_size_values as sv ON sv.attribute_id = a.attribute_id
-> LEFT JOIN eav_product_color_values as cv ON cv.attribute_id = a.attribute_idG
*************************** 1. row ***************************
attribute_id: 1
entity_type: product
attribute_name: SKU
attribute_desc: 12345
product_name: Test Product 12345
product_description: Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donec molestie urna nec tortor ultrices id viverra arcu blandit. Etiam iaculis leo at ligula semper a tincidunt est ornare. Vestibulum feugiat bibendum dapibus. Morbi eget scelerisque mauris. Sed auctor ante at tortor egestas vitae vestibulum dolor vehicula. Ut ultricies metus dolor, lobortis hendrerit nunc. Aliquam egestas ultricies sodales. Phasellus a ultricies mauris. Aenean placerat risus eros.
product_price: 5.95
product_size: NULL
product_color: NULL
1 row in set (0.00 sec)

And there we have it. Now, I know what you're thinking: "That's a lot of joins for something so simple." You're right; it is.

EAV doesn't seem suited to something as menial as this. It really shines when you have multiple entities with ever-expanding sets of attributes and value requirements. For something like products, it would be better to combine a full product table (with name, description, sku, etc) with a pricing table, product variations table, etc., in an EAV-like setup where the "entity" table is the full product table and the attribute-value tables are configured such that they relate to the products table using a product id.

To avoid altering the relational tables, you could do an "attributes" type table for size, color, retail price, sale price, etc., and then a "values" table for the value of those "attributes." This is a little more clean, but it's not a true EAV setup as there's not one central entities table. I would recommend this type of setup over an EAV setup as it just makes more sense and you can achieve higher performance through indexing and less JOIN statements.
; ?>