EAV Backend Types

Varchar Backend Type

Varchar Type

Stores short text values up to 255 characters:

-- Value table structure
CREATE TABLE catalog_product_entity_varchar (
    value_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    attribute_id SMALLINT UNSIGNED NOT NULL,
    entity_id INT UNSIGNED NOT NULL,
    store_id SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    value VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (value_id),
    INDEX IDX_ENTITY_ATTRIBUTE (entity_id, attribute_id)
) ENGINE=InnoDB;

Use Cases

-- Product name
INSERT INTO catalog_product_entity_varchar (attribute_id, entity_id, store_id, value)
VALUES (71, 123, 0, 'iPhone 15 Pro Max');

-- SKU
INSERT INTO catalog_product_entity_varchar (attribute_id, entity_id, store_id, value)
VALUES (77, 123, 0, 'IPH-15-PRO-MAX');

-- URL key
INSERT INTO catalog_product_entity_varchar (attribute_id, entity_id, store_id, value)
VALUES (118, 123, 0, 'iphone-15-pro-max');

-- Meta title
INSERT INTO catalog_product_entity_varchar (attribute_id, entity_id, store_id, value)
VALUES (119, 123, 0, 'iPhone 15 Pro Max - Best Price');

Varchar Best Practices

Use varchar for:
  âœ“ Product names
  âœ“ SKU codes
  âœ“ URL keys
  âœ“ Meta titles
  âœ“ Short labels
  âœ“ Color codes (#FF5733)

Do NOT use varchar for:
  âœ— Long descriptions (use text)
  âœ— HTML content (use text)
  âœ— Numeric values (use int/decimal)
  âœ— Dates (use datetime)

Int Backend Type

Int Type

Stores integer values, commonly used for dropdowns and booleans:

-- Value table structure
CREATE TABLE catalog_product_entity_int (
    value_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    attribute_id SMALLINT UNSIGNED NOT NULL,
    entity_id INT UNSIGNED NOT NULL,
    store_id SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    value INT DEFAULT NULL,
    PRIMARY KEY (value_id),
    INDEX IDX_ENTITY_ATTRIBUTE (entity_id, attribute_id)
) ENGINE=InnoDB;

Use Cases

-- Status (1=enabled, 2=disabled)
INSERT INTO catalog_product_entity_int (attribute_id, entity_id, store_id, value)
VALUES (97, 123, 0, 1);

-- Visibility (1=not visible, 2=catalog, 3=search, 4=catalog+search)
INSERT INTO catalog_product_entity_int (attribute_id, entity_id, store_id, value)
VALUES (99, 123, 0, 4);

-- Tax class ID
INSERT INTO catalog_product_entity_int (attribute_id, entity_id, store_id, value)
VALUES (100, 123, 0, 2);

-- Boolean (0=no, 1=yes)
INSERT INTO catalog_product_entity_int (attribute_id, entity_id, store_id, value)
VALUES (128, 123, 0, 1);

-- Dropdown option ID
INSERT INTO catalog_product_entity_int (attribute_id, entity_id, store_id, value)
VALUES (135, 123, 0, 5); -- Option ID 5

Int for Dropdowns

// Create dropdown attribute
$eavSetup->addAttribute(
    \Magento\Catalog\Model\Product::ENTITY,
    'color',
    [
        'type' => 'int',
        'input' => 'select',
        'label' => 'Color',
        'source' => 'Vendor\Module\Model\Source\Color',
    ]
);

// Source model returns option array
class Color implements \Magento\Eav\Model\Entity\Attribute\Source\Interface
{
    public function getAllOptions(): array
    {
        return [
            ['value' => 1, 'label' => __('Red')],
            ['value' => 2, 'label' => __('Blue')],
            ['value' => 3, 'label' => __('Green')],
        ];
    }
}

Decimal Backend Type

Decimal Type

Stores precise numeric values with scale and precision:

-- Value table structure
CREATE TABLE catalog_product_entity_decimal (
    value_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    attribute_id SMALLINT UNSIGNED NOT NULL,
    entity_id INT UNSIGNED NOT NULL,
    store_id SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    value DECIMAL(12,4) DEFAULT NULL,
    PRIMARY KEY (value_id),
    INDEX IDX_ENTITY_ATTRIBUTE (entity_id, attribute_id)
) ENGINE=InnoDB;

Use Cases

-- Price (scale=4, precision=12)
INSERT INTO catalog_product_entity_decimal (attribute_id, entity_id, store_id, value)
VALUES (75, 123, 0, 29.9900);

-- Special price
INSERT INTO catalog_product_entity_decimal (attribute_id, entity_id, store_id, value)
VALUES (76, 123, 0, 24.9900);

-- Weight
INSERT INTO catalog_product_entity_decimal (attribute_id, entity_id, store_id, value)
VALUES (80, 123, 0, 0.5000);

-- Tier price
INSERT INTO catalog_product_entity_decimal (attribute_id, entity_id, store_id, value)
VALUES (136, 123, 0, 19.9900);

-- Tax rate
INSERT INTO catalog_product_entity_decimal (attribute_id, entity_id, store_id, value)
VALUES (140, 123, 0, 0.2000); -- 20%

Decimal Configuration

// Create decimal attribute
$eavSetup->addAttribute(
    \Magento\Catalog\Model\Product::ENTITY,
    'custom_dimension',
    [
        'type' => 'decimal',
        'input' => 'price',
        'label' => 'Custom Dimension',
        'backend' => 'Magento\Catalog\Model\Product\Attribute\Backend\Price',
    ]
);

// Custom scale and precision in db_schema.xml
<column xsi:type="decimal" name="value" scale="4" precision="12" 
        unsigned="true" nullable="true"/>

Text, DateTime, and Static Types

Text Type

Stores long text content (up to 64KB):

-- Value table structure
CREATE TABLE catalog_product_entity_text (
    value_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    attribute_id SMALLINT UNSIGNED NOT NULL,
    entity_id INT UNSIGNED NOT NULL,
    store_id SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    value TEXT DEFAULT NULL,
    PRIMARY KEY (value_id),
    INDEX IDX_ENTITY_ATTRIBUTE (entity_id, attribute_id)
) ENGINE=InnoDB;

-- Product description
INSERT INTO catalog_product_entity_text (attribute_id, entity_id, store_id, value)
VALUES (73, 123, 0, '<p>This is a detailed product description with <strong>HTML</strong> content...</p>');

-- Short description
INSERT INTO catalog_product_entity_text (attribute_id, entity_id, store_id, value)
VALUES (74, 123, 0, '<p>Brief product summary</p>');

DateTime Type

-- Value table structure
CREATE TABLE catalog_product_entity_datetime (
    value_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    attribute_id SMALLINT UNSIGNED NOT NULL,
    entity_id INT UNSIGNED NOT NULL,
    store_id SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    value DATETIME DEFAULT NULL,
    PRIMARY KEY (value_id),
    INDEX IDX_ENTITY_ATTRIBUTE (entity_id, attribute_id)
) ENGINE=InnoDB;

-- Special price from date
INSERT INTO catalog_product_entity_datetime (attribute_id, entity_id, store_id, value)
VALUES (101, 123, 0, '2024-01-01 00:00:00');

-- Special price to date
INSERT INTO catalog_product_entity_datetime (attribute_id, entity_id, store_id, value)
VALUES (102, 123, 0, '2024-12-31 23:59:59');

-- News from date
INSERT INTO catalog_product_entity_datetime (attribute_id, entity_id, store_id, value)
VALUES (103, 123, 0, '2024-06-01 00:00:00');

Static Type

Stored directly in the entity table (not in value tables):

-- Static columns in catalog_product_entity
ALTER TABLE catalog_product_entity
    ADD COLUMN type_id VARCHAR(32) NOT NULL DEFAULT 'simple',
    ADD COLUMN attribute_set_id SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    ADD COLUMN sku VARCHAR(255) DEFAULT NULL,
    ADD COLUMN has_options SMALLINT NOT NULL DEFAULT 0,
    ADD COLUMN required_options SMALLINT NOT NULL DEFAULT 0;

-- These columns are directly in the entity table
-- No JOINs needed for access
SELECT entity_id, sku, type_id, attribute_set_id 
FROM catalog_product_entity 
WHERE entity_id = 123;

Type Comparison

Type	Storage	Max Size	Use Cases
varchar	*_entity_varchar	255 chars	Name, SKU, URL key
text	*_entity_text	64KB	Description, HTML
int	*_entity_int	2^31	Status, dropdowns, booleans
decimal	*_entity_decimal	12,4	Price, weight, rates
datetime	*_entity_datetime	DATETIME	Dates, timestamps
static	Entity table	Varies	Core entity columns

Flashcards

Question

What is varchar backend type?

Click to reveal answer

Answer

Short text up to 255 chars: name, SKU, URL key

Question

What is int backend type?

Click to reveal answer

Answer

Integer values: status, visibility, dropdown options, booleans

Question

What is decimal backend type?

Click to reveal answer

Answer

Precise numeric: price (12,4), weight, tax rates

Question

What is text backend type?

Click to reveal answer

Answer

Long text up to 64KB: descriptions, HTML content

Question

What is static backend type?

Click to reveal answer

Answer

Stored directly in entity table, no value table needed

Revision Notes

Key Takeaways

1. varchar: Short text (255 chars) - name, SKU, URL key
2. int: Integer values - status, dropdowns, booleans
3. decimal: Precise numeric (12,4) - price, weight
4. text: Long text (64KB) - descriptions, HTML
5. datetime: Date/time values - special price dates
6. static: Entity table columns - no value table needed

Interview Tips

• Explain when to use each backend type
• Discuss the trade-offs between varchar and text
• Describe how static attributes differ from EAV values

Cheat Sheet

Backend Types:
  varchar   â†’ VARCHAR(255), short text
  int       â†’ INT, integers/dropdowns
  decimal   â†’ DECIMAL(12,4), prices
  text      â†’ TEXT, long content
  datetime  â†’ DATETIME, dates
  static    â†’ Entity table columns

Use Cases:
  varchar: name, sku, url_key, meta_title
  int: status, visibility, tax_class, dropdowns
  decimal: price, special_price, weight, tier_price
  text: description, short_description, cms_content
  datetime: special_price_from, special_price_to
  static: type_id, attribute_set_id, sku

Varchar Backend Type

Varchar Type

Use Cases

Varchar Best Practices

Int Backend Type

Int Type

Use Cases

Int for Dropdowns

Decimal Backend Type

Decimal Type

Use Cases

Decimal Configuration

Text, DateTime, and Static Types

Text Type

DateTime Type

Static Type

Type Comparison

Quiz

Flashcards

Revision Notes

Key Takeaways

Interview Tips

Cheat Sheet