Magento EAV Database Structure

In this blog we will look into magento database structure specially the EAV modules used to store product, category, and customers.

First we look at a general understanding of EAV model and how it’s used. Then we will look into how magento uses EAV model for its entities.In this blog we will specifically look only Attributes Model in magento, then in subsequent blogs we will look into other entities.

General Explanation of EAV model

EAV Database model mean Entity Attribute Value model, you can look at a description about it here.
Here are the basics of EAV model of database as I understand it. In the Relational model of database tables, for each attribute of an entity we create additional columns in a table. For example, we have a user entity which has attribute firstname,lastname,address then in traditional table structure we would create a table with name ‘user’ and add columns to the table named firstname,lastname,address. Maybe a few more columns like Primary Key or Foreign Key depending on our business requirements.

General User Table

Relational model - User Table

But suppose you have an entity in which the number of attribute/columns is very large lets say 100 and on top of that only few attributes are required i.e when we gather data about that entity out of the 100 columns around 10% of the columns we get data, rest 90% we rarely get data. In such a case our traditional table would have a single table with 100 columns and most the columns would be empty or filled with NULL values. This would be a very bad table structure for such a data. In such case, we use EAV database model for tables.
In EAV database model, we have a separate table which store attributes of an entity and separate tables where we store attribute values. Like if we take example of the previous ‘user’ entity but let’s assume there are 100 attributes of a user. Let’s assume there are few basic attributes which are required for all user like firstname, lastname and then there are many other attributes like address, pincode, facebook profile id, schoolname,dob, etc. We would need a table for User entity called user_entity which would contain the base user data. The columns would be id,firstname,lastname
user_entity table

user_entity table

We would have a separate table called user_attribute with columns id and name
user_attribute table

user_attribute table

And another table called user_values with column id,attribute_id,user_id,value
user_values table

user_values table

So the ‘user_attribute’, will have all attributes of user.
So, when we want to enter a user data in tables, we will make entries in two tables. First is ‘user_entity’ and second is ‘user_values’. ‘user_entity’ will have only the required values, and ‘user_values’ will have the values of only those attribute for which we were able to gather database.
So, this is the basic idea of EAV table structure.

EAV Tables in Magento DB

So from the above explanation we have understand the basic concept on EAV tables, now lets see how it’s implemented in magento. For any EAV tables we need an attribute table, an entity table and entity value table. The main attribute table in magento is the ‘eav_attribute’ table. In this table all attributes for all the entities are stored.
Lets first look into detail of how attributes are saved in magento. i.e we have a Manage Attribute and Manage Attribute Set interface in magento admin. Let see how the database for this looks.
Magento Attributes Database Tables

eav_attributes table

eav_attributes table

Let go through each field in this table.

  • attribute_id: this is the primary key
  • entity_type_id: this is a foreign key of table eav_entity_type. This basically contains id of entity type like customer or product or category etc. i.e this particular attribute belongs to which entity
  • attribute_code: this is a unique code used to identity an attribute. Basically, a human understandable unique id other than the primary key
  • attribute_model:
  • backend_model: this usually has the path of a model class. These model classes are used when saving attribute value in database. Like for example saving of product media images requires some operating to happen before and after save. So those operations are defined in these classes
  • backend_type: this specifies that data type of the attribute. Magento has 6 types static,datetime,int,text,varchar. The static type is special. The attributes with static type are stored in main entity table
  • backend_table: this is used when attribute values are to be stored in some special table, rather than default magento tables
  • backend_label: this has the label of the attribute as shown in admin.
  • fontend_model: this class is used to change the display format of the values if required. For example, Mage_Eav_Model_Entity_Attribute_Frontend_Datetime has a getValue() which is used to format dates
  • fontend_input: this sets how an attribute should be displayed on frontend. Weather it should be a text or dropdown or date etc
  • fontend_label: this is the label used in frontend when the attribute is displayed
  • fontend_class:
  • source_model: this is a path to a model, from which values are taken. For example, if attribute type is select. Then the options in the dropdown menu are shown from this class
  • is_required: weather is compulsory for admin to fill up this value
  • is_user_defined: weather is user-generated attribute or magento system attributes
  • default_value: any default value of an attribute
  • is_unique: if the values of these attributes should be unique
  • note: any comments or personal note

This the main table, where all the attributes created from Manage Attributes and other magento attributes stored.
Another table which is being used as a foreign key for “entity_type_id” column is “eav_entity_type”. This table has many columns but I have just shown the important columns.

eav_entity_table table

eav_entity_table table

  • entity_type_id: this is the primary key of the table, and also used as the foreign key in entity_type_id column in eav_attribute table
  • entity_type_code: these are unique codes use to specifies varies entities for wich the EAV table structure is used
  • entity_model: the base magento model class for that entity
  • attribute_model: there are attribute_model classes. If no attribute model class is given the default eav/attribute is taken
  • entity_table: the based entity database table name, where the required attributes of are entity are stored

This table stores the entity type id, of the different entities in magento. Entity type id used through magento db, to identify an entity.

Next table we will look is ‘eav_attribute_set’ table.

eav_attribute_set table

eav_attribute_set table

  • attribute_set_id: this is the primary key of the table
  • entity_type_id: this is a foreign key to the table eav_entity_type. This associates the attribute set name to the entity type.
  • attribute_set_name: the name of the attribute set
  • sort_order: order in which attribute sets are shown in admin.

This table is used to store the attribute sets we created from Manage Attribute Sets in magento admin. As you can see the default attribute set table, all Default attributes sets have been created for entity types.
Next table we will look at is the “eav_attribute_group” table.

eav_attribute_group table

eav_attribute_group table

  • attribute_group_id: primary key
  • attribute_set_id: foreign key of the eav_attribute_set table
  • attribute_group_name: name of the group
  • sort_order: display order of groups
  • default_id:

This table is used to store the group that we create inside an attribute set. Like if you open the Default attribute set in product you will see many groups like General, Prices, etc as shown here See Screenshot

The next table which we have is “eav_attribute_label”

eav_attribute_lable table

eav_attribute_lable table

This table is used to store the values of labels when we use the multiple store option, as show hereSee Screenshot. We know that magento supports multiple stores. The eav_attribute table only allows us to store values of frontend_lable and backend_lable. So the labels for other stores are stored in this table.
As seen in the image, the label given in Store2,Store3 etc are stored in this table.

  • attribute_label_id: primary key
  • attribute_id: foreign key of eav_attribute table
  • store_id: foreign key of store table.
  • value: is the actual label

Next table we will see is the eav_attribute_option table

eav_attribute_option table

eav_attribute_option table

This table Is used to for drop down attribute types only. Specifically used to store the drop down options which we enter in adminSee Screenshot. So for each option created a new row is added to the eav_attribute_option table and then the values are stored in another table called eav_attribute_option_value
eav_attribute_option_value table

eav_attribute_option_value table

Here as you can see the values are stored based on the option_id and store_id.
Next to look at is eav_entity_attribute table
eav_entity_attribute table

eav_entity_attribute table

This table contains all the relations between an attribute, its attribute group and attribute set. The column names are pretty clear as to what they indicate.

So, in the above tables we have seen how only the attributes are stored in magento database.
In the next article we will see how a Product is stored in magento table using the EAV architecture