How to add fields by category when using EAV?

Question

MySQL

I want to write a simple online store (in Yii). Of course, products must have arbitrarily added fields, for which the Entity-Attribute-Value pattern is used. How to make sure that field types are tied to categories, and values ​​- to specific products?
That is, let's say, the site admin, when creating a product card, selects a category (let's say, roughly speaking, "hard drives"), and fields specific to this category appear ("volume", "spindle speed", etc. .), and are filled specifically for this product. And the names / types of fields should be specified when creating / editing a category, not a product.

Answer 1

Relational MySQL in your case is not the best option, or rather not at all suitable. EAV is the opposite of having a data schema, and without a schema you are in NoSQL, or, the simplest and fastest and most complete option is Postgres with jsonb and indexes on it.

Answer 2

I did this:
There is a table with sections, there is a table with products, and there is a table with product property values.
Products and properties have a field with the id of the section by which they are related. And in the table with product property values, there are fields for product id and property id.
Later I added another table with lists and list values ​​so that you can select values ​​from the prepared ones. As a result, the values ​​of product properties began to refer also to the values ​​of the lists.