EAV vs Custom Tables

EAV (Entity-Attribute-Value) Model

-- EAV tables structure
CREATE TABLE catalog_product_entity (
    entity_id INT AUTO_INCREMENT PRIMARY KEY,
    attribute_set_id INT,
    type_id VARCHAR(32),
    sku VARCHAR(255)
);

CREATE TABLE catalog_product_entity_varchar (
    value_id INT AUTO_INCREMENT PRIMARY KEY,
    attribute_id INT,
    entity_id INT,
    store_id INT DEFAULT 0,
    value VARCHAR(255)
);

CREATE TABLE catalog_product_entity_int (
    value_id INT AUTO_INCREMENT PRIMARY KEY,
    attribute_id INT,
    entity_id INT,
    store_id INT DEFAULT 0,
    value INT
);

-- Query requires JOINs
SELECT p.*, pv.value AS name, pi.value AS quantity
FROM catalog_product_entity p
LEFT JOIN catalog_product_entity_varchar pv 
    ON p.entity_id = pv.entity_id AND pv.attribute_id = 71
LEFT JOIN catalog_product_entity_int pi 
    ON p.entity_id = pi.entity_id AND pi.attribute_id = 78;

Custom Tables

-- Flat table structure
CREATE TABLE custom_product (
    entity_id INT AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(255),
    name VARCHAR(255),
    quantity INT,
    price DECIMAL(10,2),
    status TINYINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Simple query
SELECT * FROM custom_product WHERE entity_id = 123;

EAV Flexibility

// Add new attribute without schema change
$eavSetup->addAttribute(
    \Magento\Catalog\Model\Product::ENTITY,
    'custom_field', [
        'type' => 'text',
        'label' => 'Custom Field',
        'input' => 'text',
        'required' => false,
        'global' => \Magento\Eav\Model\Entity\Attribute\ScopedAttributeInterface::SCOPE_STORE,
        'group' => 'General',
    ]
);

// Dynamic attribute access
$product->setData('custom_field', 'value');
$value = $product->getData('custom_field');

// Per-store values
$product->setData('custom_field', 'en_value', 1); // English
$product->setData('custom_field', 'fr_value', 2); // French

Custom Table Performance

// Performance comparison
// EAV: 5 JOINs for full product data
$products = $this->resourceModel->getCollection()
    ->addAttributeToSelect('*')
    ->load();
// 50ms for 100 products

// Custom table: Single query
$products = $this->customProductCollection->getCollection()
    ->load();
// 5ms for 100 products

// 10x performance improvement with custom tables

Query Complexity

-- EAV: Complex JOINs
SELECT p.entity_id, p.sku,
    MAX(CASE WHEN pv.attribute_id = 71 THEN pv.value END) AS name,
    MAX(CASE WHEN pv.attribute_id = 72 THEN pv.value END) AS description,
    MAX(CASE WHEN pi.attribute_id = 78 THEN pi.value END) AS qty
FROM catalog_product_entity p
LEFT JOIN catalog_product_entity_varchar pv ON p.entity_id = pv.entity_id
LEFT JOIN catalog_product_entity_int pi ON p.entity_id = pi.entity_id
WHERE p.entity_id = 123
GROUP BY p.entity_id;

-- Custom: Simple query
SELECT * FROM custom_product WHERE entity_id = 123;

Use EAV When:

• Attributes vary per product type
• Per-store attribute values needed
• Dynamic attribute creation required
• Attribute sets vary by product type
• Extension without database changes
• Multi-store/multi-language support

Use Custom Tables When:

• Fixed, known attributes
• Performance is critical
• Complex queries with joins needed
• Reporting and analytics required
• Data relationships are fixed
• High-volume reads

Hybrid Approach

// Core data in custom table, extended in EAV
class ProductRepository
{
    public function get($id)
    {
        // Core data from flat table (fast)
        $product = $this->flatTableResource->getById($id);
        
        // Extended data from EAV (flexible)
        $eavData = $this->eavResource->load($id);
        
        // Merge data
        $product->addData($eavData->getData());
        return $product;
    }
}

// Flat table for catalog, EAV for custom attributes
// Performance for common queries
// Flexibility for extensions

Magento's Approach

// Magento uses flat catalog for performance
// EAV for flexibility
// Catalog search uses flat tables
// Product API uses EAV

// Configuration
// Catalog > Catalog > Use Flat Catalog Product = Yes
// Catalog > Catalog > Use Flat Catalog Category = Yes

// Flat table update on save
$this->flatTableResource->save($product);
$this->catalogResource->reindex($product->getId());

Custom Table Implementation

// Schema definition
// app/code/Vendor/Module/etc/db_schema.xml
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="custom_product" resource="default" engine="innodb">
        <column xsi:type="int" name="entity_id" padding="10" unsigned="true" nullable="false" identity="true" comment="Entity ID"/>
        <column xsi:type="varchar" name="sku" nullable="false" length="255" comment="SKU"/>
        <column xsi:type="varchar" name="name" nullable="false" length="255" comment="Name"/>
        <column xsi:type="int" name="qty" unsigned="true" nullable="false" comment="Quantity"/>
        <column xsi:type="decimal" name="price" scale="4" precision="12" unsigned="false" nullable="false" comment="Price"/>
        <constraint referenceType="unique" xsi:type="unique" referenceId="CUSTOM_PRODUCT_SKU_UNIQUE">
            <column name="sku"/>
        </constraint>
        <index referenceId="CUSTOM_PRODUCT_QTY" indexType="btree">
            <column name="qty"/>
        </index>
    </table>
</schema>

EAV Extension

// Add custom attribute via setup script
$eavSetup = $this->eavSetupFactory->create(['setup' => $setup]);

$eavSetup->addAttribute(
    \Magento\Catalog\Model\Product::ENTITY,
    'custom_weight', [
        'type' => 'decimal',
        'label' => 'Custom Weight',
        'input' => 'text',
        'required' => false,
        'global' => \Magento\Eav\Model\Entity\Attribute\ScopedAttributeInterface::SCOPE_STORE,
        'group' => 'General',
    ]
);

// Use in product
$product->setCustomWeight(1.5);
$product->save();

// Query with attribute
$collection = $this->productCollection->create();
$collection->addAttributeToFilter('custom_weight', ['gt' => 1.0]);