Magento 2. What is the purpose of the catalog_eav_attribute table?

Question

What is the purpose of the catalog_eav_attribute table?

Is it in any way related to categories and category attributes? If so, how?

Answer 1

catalog_eav_attribute is an extension of the eav_attribute table.
The eav_attribute table contains only the general data for all the attributes of all the EAV entities (category, product, customer, customer address, anything custom).
But each EAV entity requires different data for their attributes.
Example "Used in layered navigation". This has no value for the customer attributes but it does for products.
The values in catalog_eav_attribute table apply to products and categories attributes only.
Each line in the catalog_eav_attribute table has a corresponding line in the eav_attribute table. The link is made based on the attribute_id field.
Each attribute's properties are determined by the fields in the eav_attribute table plus the fields in catalog_eav_attribute.

In the same way, there is a table called customer_eav_attribute that serves the same purpose for the customer and customer address attributes.

Answer 2

catalog_eav_attribute is the table where the attribute id will be saved if you create any custom eav attribute using the \Magento\Catalog\Model**::Enity. Something like this code:-

$eavSetup->addAttribute(
            \Magento\Catalog\Model\Product::ENTITY,
            'yourattr',
            [
                'type'                    => 'varchar',
                'backend'                 => '',
                'frontend'                => '',
                'label'                   => 'yourattr',
                'input'                   => '',
                'class'                   => '',
                'source'                  => '',
                'global'                  => \Magento\Eav\Model\Entity\Attribute\ScopedAttributeInterface::SCOPE_GLOBAL,
                'visible'                 => false,
                'required'                => false,
                'user_defined'            => false,
                'is_system'               => true,
                'default'                 => '',
                'searchable'              => false,
                'filterable'              => false,
                'comparable'              => false,
                'visible_on_front'        => false,
                'used_in_product_listing' => true,
                'unique'                  => false,
                'apply_to'                => '',
            ]
        );

The value of this is actually saved in eav_attribute table

I am not sure if they are related directly to categories because categories are different entity type you can check in eav_entity_type table

But if you will further check in the vendor\magento\module-catalog\Setup\CategorySetup.php file, you will find that CATEGORY_ENTITY_TYPE_ID constant is related to additional table catalog_eav_attribute

Refer to this attribute code default_sort_by in the eav_attribute table, so this will be formed if you use the setup code something like this:-

$eavSetup->addAttribute(
                \Magento\Catalog\Model\Category::ENTITY,
                'yourattrforcategory',

Answer 3

How it works…

Installing a product attribute is done in the data install or upgrade scripts. In that script, we use the categorySetup class to add the attributes. In this class, the addAttribute() method is available for adding attributes.

The addAttribute() method will create an EAV attribute that will be stored in the table eav_attribute. Every EAV attribute is from a specific entity type. That’s the reason why we have retrieved the entity type with the method $categorySetup>getEntityTypeId(\Magento\Catalog\Model\Product::ENTITY);.

For a product, some information of the attribute (such as the visible_on_front, used_in_product_listing, and more) is stored in a separate table. This table is the catalog_eav_attribute table where all the extended information of the catalog attributes is saved. With the attribute_id parameter, every row is related to an EAV attribute from the table eav_attribute.