EAV Attribute Tables

eav_entity_type Table

Entity Type Definitions

The eav_entity_type table defines all EAV entity types in Magento:

SELECT * FROM eav_entity_type;
+----------------+----------------------+--------------------+----------------+
| entity_type_id | entity_type_code     | entity_model       | entity_table    |
+----------------+----------------------+--------------------+----------------+
|              1 | customer             | customer/entity    | customer_entity |
|              2 | customer_address    | customer/entity    | customer_address|
|              3 | catalog_category    | catalog/category   | catalog_category|
|              4 | catalog_product     | catalog/product    | catalog_product|
|              5 | order               | sales/order        | sales_order     |
|              7 | invoice             | sales/order_invoice| sales_invoice   |
|              8 | creditmemo          | sales/order_creditmemo| sales_creditmemo|
|              9 | shipment            | sales/order_shipment| sales_shipment |
+----------------+----------------------+--------------------+----------------+

Entity Type Structure

CREATE TABLE eav_entity_type (
    entity_type_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Entity Type ID',
    entity_type_code VARCHAR(255) NOT NULL COMMENT 'Entity Type Code',
    entity_model VARCHAR(255) NOT NULL COMMENT 'Entity Model',
    entity_attribute_model VARCHAR(255) DEFAULT NULL COMMENT 'Entity Attribute Model',
    entity_table VARCHAR(255) DEFAULT NULL COMMENT 'Entity Table',
    value_table_prefix VARCHAR(255) DEFAULT NULL COMMENT 'Value Table Prefix',
    entity_id_field VARCHAR(255) DEFAULT NULL COMMENT 'Entity ID Field',
    data_model VARCHAR(255) DEFAULT NULL COMMENT 'Data Model',
    increment_model VARCHAR(255) DEFAULT NULL COMMENT 'Increment Model',
    increment_per_store SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Increment Per Store',
    NONE VARCHAR(255) DEFAULT NULL COMMENT 'None',
    event_prefix VARCHAR(255) DEFAULT NULL COMMENT 'Event Prefix',
    PRIMARY KEY (entity_type_id),
    UNIQUE INDEX IDX_ENTITY_TYPE_CODE (entity_type_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='EAV Entity Type';

// Get entity type info
$entityType = $objectManager->get(
    \Magento\Eav\Model\Entity\TypeFactory::class
)->create()->load(4); // catalog_product

echo $entityType->getEntityModel();    // catalog/product
echo $entityType->getEntityTable();     // catalog_product_entity
echo $entityType->getValueTablePrefix(); // catalog_product_entity_

eav_attribute Table

Attribute Definitions

CREATE TABLE eav_attribute (
    attribute_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Attribute ID',
    entity_type_id SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Entity Type ID',
    attribute_code VARCHAR(255) NOT NULL COMMENT 'Attribute Code',
    attribute_model VARCHAR(255) DEFAULT NULL COMMENT 'Attribute Model',
    backend_model VARCHAR(255) DEFAULT NULL COMMENT 'Backend Model',
    backend_type VARCHAR(8) NOT NULL DEFAULT 'static' COMMENT 'Backend Type',
    backend_table VARCHAR(255) DEFAULT NULL COMMENT 'Backend Table',
    frontend_model VARCHAR(255) DEFAULT NULL COMMENT 'Frontend Model',
    frontend_input VARCHAR(255) DEFAULT NULL COMMENT 'Frontend Input',
    frontend_label VARCHAR(255) DEFAULT NULL COMMENT 'Frontend Label',
    frontend_class VARCHAR(255) DEFAULT NULL COMMENT 'Frontend Class',
    source_model VARCHAR(255) DEFAULT NULL COMMENT 'Source Model',
    default_value TEXT DEFAULT NULL COMMENT 'Default Value',
    is_required SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Is Required',
    is_user_defined SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Is User Defined',
    is_unique SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Is Unique',
    is_filterable SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Is Filterable',
    is_filterable_in_search SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Is Filterable in Search',
    is_used_for_promo_rules SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Is Used for Promo Rules',
    is_html_allowed_on_front SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Is HTML Allowed on Front',
    is_visible_on_front SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Is Visible on Front',
    is_used_for_sort_by SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Is Used for Sort by',
    applied_store SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Applied Store',
    is_searchable SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Is Searchable',
    is_visible_in_advanced_search SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Is Visible in Advanced Search',
    position SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Position',
    is_wysiwyg_enabled SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Is WYSIWYG Enabled',
    is_used_for_rule_based SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Is Used for Rule Based',
    is_comparable SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Is Comparable',
    `scope` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Scope',
    is_required_in_admin_store SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Is Required in Admin Store',
    is_used_for_grid SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Is Used for Grid',
    is_ajax_update Enabled SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Is Ajax Update Enabled',
    is_configurable SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Is Configurable',
    is_visible_in_advanced_search SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Is Visible in Advanced Search',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Created At',
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Updated At',
    PRIMARY KEY (attribute_id),
    UNIQUE INDEX IDX_ATTRIBUTE_CODE (attribute_code),
    INDEX IDX_ENTITY_TYPE_ID (entity_type_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='EAV Attribute';

Common Product Attributes

SELECT attribute_id, attribute_code, backend_type, frontend_input, is_required
FROM eav_attribute
WHERE entity_type_id = 4
ORDER BY attribute_id
LIMIT 20;
+---------------+------------------+--------------+-----------------+-------------+
| attribute_id  | attribute_code   | backend_type | frontend_input  | is_required |
+---------------+------------------+--------------+-----------------+-------------+
|            71 | name             | varchar      | text            |           1 |
|            73 | description      | text         | textarea        |           0 |
|            74 | short_description| text         | textarea        |           0 |
|            75 | price            | decimal      | price           |           1 |
|            77 | sku              | varchar      | text            |           1 |
|            78 | image            | varchar      | media_image     |           0 |
|            85 | small_image      | varchar      | media_image     |           0 |
|            86 | thumbnail        | varchar      | media_image     |           0 |
|            97 | status           | int          | boolean         |           1 |
|            99 | visibility       | int          | select          |           1 |
|           100 | tax_class_id     | int          | select          |           0 |
+---------------+------------------+--------------+-----------------+-------------+

Backend Types

Type	Value Table	Use Case
varchar	*_entity_varchar	Short text, SKU, URL key
text	*_entity_text	Long descriptions, HTML
int	*_entity_int	Status, visibility, dropdowns
decimal	*_entity_decimal	Price, weight, dimensions
datetime	*_entity_datetime	Created date, special price from
static	(in entity table)	Columns in entity table itself

Attribute Options

Select Attribute Options

For select/boolean attributes, options are stored separately:

-- Option definitions
CREATE TABLE eav_attribute_option (
    option_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Option ID',
    attribute_id SMALLINT UNSIGNED NOT NULL COMMENT 'Attribute ID',
    sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Sort Order',
    PRIMARY KEY (option_id),
    INDEX IDX_ATTRIBUTE_ID (attribute_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='EAV Attribute Option';

-- Option values (multi-language)
CREATE TABLE eav_attribute_option_value (
    value_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Value ID',
    option_id INT UNSIGNED NOT NULL COMMENT 'Option ID',
    store_id SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Store ID',
    value VARCHAR(255) DEFAULT NULL COMMENT 'Value',
    PRIMARY KEY (value_id),
    INDEX IDX_OPTION_ID (option_id),
    INDEX IDX_STORE_ID (store_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='EAV Attribute Option Value';

Querying Options

-- Get options for 'status' attribute (attribute_id = 97)
SELECT 
    o.option_id,
    v.value AS option_label,
    o.sort_order
FROM eav_attribute_option o
JOIN eav_attribute_option_value v ON v.option_id = o.option_id
WHERE o.attribute_id = 97 AND v.store_id = 0
ORDER BY o.sort_order;
+-----------+--------------+------------+
| option_id | option_label | sort_order |
+-----------+--------------+------------+
|         1 | Enabled      |          1 |
|         2 | Disabled     |          2 |
+-----------+--------------+------------+

-- Get options for 'visibility' attribute (attribute_id = 99)
SELECT o.option_id, v.value AS label
FROM eav_attribute_option o
JOIN eav_attribute_option_value v ON v.option_id = o.option_id
WHERE o.attribute_id = 99 AND v.store_id = 0;
+-----------+---------------------------+
| option_id | label                     |
+-----------+---------------------------+
|         1 | Not Visible Individually  |
|         2 | In Catalog                 |
|         3 | In Search Only             |
|         4 | Catalog and Search         |
+-----------+---------------------------+

Using Options in PHP

// Get attribute options
$attribute = $eavConfig->getAttribute('catalog_product', 'status');

// Get all options
$options = $attribute->getSource()->getAllOptions();
// Returns: [['value' => 1, 'label' => 'Enabled'], ['value' => 2, 'label' => 'Disabled']]

// Get option by value
$label = $attribute->getSource()->getOptionText(1); // 'Enabled'

// Custom source model
class Vendor\Module\Model\Source\CustomOptions
{
    public function toOptionArray(): array
    {
        return [
            ['value' => 'option1', 'label' => __('Option One')],
            ['value' => 'option2', 'label' => __('Option Two')],
        ];
    }
}

Attribute Scope and Configuration

Attribute Scopes

Attributes can store values per scope:

// Scope values
const SCOPE_STORE = 0;      // Different value per store view
const SCOPE_WEBSITE = 1;    // Different value per website
const SCOPE_GLOBAL = 2;     // Same value across all stores

// Example: price is website-scoped, name is store-scoped
// catalog_product_entity_varchar:
// entity_id=1, attribute_id=name, store_id=0: 'T-Shirt'
// entity_id=1, attribute_id=name, store_id=1: 'Tee-Shirt' (French)

// catalog_product_entity_decimal:
// entity_id=1, attribute_id=price, store_id=0: 29.99 (base)
// entity_id=1, attribute_id=price, store_id=1: 29.99 (inherited from default)

Attribute Metadata in XML

<!-- app/code/Vendor/Module/etc/eav_attributes.xml -->
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Eav/etc/eav_attributes.xsd">
    <entity type="catalog_product">
        <attribute id="custom_text"
            code="custom_text"
            type="varchar"
            backend="Vendor\Module\Model\Product\Attribute\Backend\Custom"
            source="Vendor\Module\Model\Product\Attribute\Source\Options"
            frontend="Vendor\Module\Model\Product\Attribute\Frontend\Custom"
            label="Custom Text"
            required="0"
            unique="0"
            searchable="1"
            filterable="1"
            comparable="0"
            visible_on_front="1"
            used_in_product_listing="1"
            is_used_for_promo_rules="0"
            is_configurable="1"
            apply_to="simple,configurable"
            group="General"
            sort_order="100"
        />
    </entity>
</config>

Key Attribute Properties

Property	Description	Example
backend_type	Data storage type	varchar, int, decimal, text, datetime
frontend_input	Admin input type	text, textarea, select, boolean, price
source_model	Provides dropdown options	eav/entity_source_boolean
backend_model	Processes value on save	eav/entity_backend_default
frontend_model	Renders on frontend	eav/entity_frontend_boolean
is_required	Must have value	0 or 1
is_filterable	Shown in layered nav	0 or 1
is_searchable	Included in search	0 or 1
scope	Value storage scope	store, website, global

Flashcards

Question

What is eav_entity_type?

Click to reveal answer

Answer

Table defining entity types: codes, models, tables, and configurations

Question

What is eav_attribute?

Click to reveal answer

Answer

Table storing attribute definitions: code, type, input, source, backend, frontend

Question

What are the backend types?

Click to reveal answer

Answer

varchar, text, int, decimal, datetime, static

Question

Where are select options stored?

Click to reveal answer

Answer

eav_attribute_option (metadata) + eav_attribute_option_value (labels per store)

Question

What is attribute scope?

Click to reveal answer

Answer

Whether values are stored per store (0), website (1), or global (2)

Revision Notes

Key Takeaways

1. eav_entity_type maps entity types to their models and tables
2. eav_attribute defines all attribute properties: code, type, input, source, scope
3. Backend types determine value table: varchar, int, decimal, text, datetime, static
4. Select options stored in eav_attribute_option + eav_attribute_option_value
5. Attribute scope controls per-store, per-website, or global storage

Interview Tips

• Explain how attribute options are stored and retrieved
• Discuss the difference between backend_type and frontend_input
• Describe how attribute scope affects multi-store setups

Cheat Sheet

Attribute Tables:
  eav_entity_type         â†’ Entity type definitions
  eav_attribute           â†’ Attribute metadata
  eav_attribute_option    â†’ Option IDs
  eav_attribute_option_value â†’ Option labels per store

Backend Types:
  varchar â†’ Short text (name, SKU)
  text    â†’ Long text (description)
  int     â†’ Integer (status, visibility)
  decimal â†’ Decimal (price, weight)
  datetime â†’ Date (special_price_from)
  static  â†’ In entity table itself

Scopes:
  0 = Store view
  1 = Website
  2 = Global

eav_entity_type Table

Entity Type Definitions

Entity Type Structure

eav_attribute Table

Attribute Definitions

Common Product Attributes

Backend Types

Attribute Options

Select Attribute Options

Querying Options

Using Options in PHP

Attribute Scope and Configuration

Attribute Scopes

Attribute Metadata in XML

Key Attribute Properties

Quiz

Flashcards

Revision Notes

Key Takeaways

Interview Tips

Cheat Sheet