EAV means Entity Attribute Value this allows us to add unlimited columns or attributes to our table virtually. Means, the fields which is represented in “column” way in a regular table, is represented in a “row” (records) way in EAV. In EAV, we have one table which holds all the “attribute” (table field names) data, and other tables which hold the “entity” (id or primary id) and value (value for that id) against each attribute.
In Magento, there is one table to hold attribute values called eav_attribute and 5-6 tables which holds entity and data in fully normalized form,
eav_entity, eav_entity_int (for holding Integer values),
- eav_entity_varchar (for holding Varchar values),
- eav_entity_datetime (for holding Datetime values),
- eav_entity_decimal (for holding Decimal/float values),
- eav_entity_text (for holding text (mysql Text type) values).
EAV is expensive and should only be used when you are not sure about number of fields in a table which can vary in future. To just get one single record, Magento joins 4-5 tables to get data in EAV. But this doesn’t mean that EAV only has drawbacks. The main advantage of EAV is when you may want to add table field in future, when there are thousands or millions of records already present in your table. In regular table, if you add table field with these amount of data, it will screw up your table, as for each empty row also some bytes will be allocated as per data type you select. While in EAV, adding the table column will not affect the previously saved records (also the extra space will not get allocated!) and all the new records will seamlessly have data in these columns without any problem.