How to deal with the EAV architecture, where each value type has its own table?

Question

PHP

I decided to use the EAV architecture for the online store, or rather for storing the characteristics of goods.
The simplest way to store all values ​​in string formats. But this is long and not entirely correct. I read on the Internet that for each value type you need to create your own table, for example, stringAttrValue, intAttrValue, textAreaAttrValue.
Accordingly, we must have a table of type - id, attr_id, attr_type. The very problem is in the attr_type field, or rather, in what form to store the value type - either, as I read, int, textarea, string, or create another table with a list of value types and already take the id of the value type.
And after that, you have to write a lot of joins to get all the values ​​from each table of the characteristic value type?

Answer 1

Instead of additional tables for each type, you can create additional columns.

entity_id       # INT, ссылка на объект, которому принадлежит свойство
attribute_id    # INT, ссылка на инфу о свойстве
value_int       # INT, значение целочисленное
value_varchar   # VARCHAR, короткий текст
value_text      # TEXT, длинный текст
value_whatever  # и так далее: DATETIME, ENUM, DECIMAL

This was done earlier in Bitrix, for example (I don’t know how now). With this approach, we can collect the properties of a separate entity in one query (without unions), but the table turns out to be sparse, most of the values ​​are NULLs.
A separate table per type is created, for example, in Magento (again, you need to check, but it used to be like this, and they are unlikely to radically change the schema). You can see what queries their ORM generates.
Well, in general, pick up different e-commerce products, EAV is implemented in some form for everyone.
If it's the writing of joins that scares you, you're scared in vain, just wrap them in some kind of API, because writing this by hand is tedious and leads to errors.
If schema performance scares you - well, ideally you should test against your data with your scripts. If you're too lazy or don't have any data or scripts yet ;) google the bundle / module for your framework and dance from it.