How to solve the problem of types in a many-to-many relationship?

Question

MySQL

The screenshot shows an example of a many-to-many relationship. Products can have different parameters, and parameters can have different products.

I can’t figure out how to solve the type problem if the value field in the options table can be of different types? For example, I have a record where value TEXT(2000), respectively, this field will not fit into the current VARCHAR(255).
The only thing that came to my mind was to make a separate table with the product_id (INT), value (TEXT) fields. But I don't really like this solution.
Has anyone encountered similar problems?

Answer 1

It all depends on how you are going to use this value.
If you don’t need to do a search on it, index it, then you can do MAX, and store the value type in it, something like {"$type":"int","value":3}
If you still need to use it will make a selection, then I would probably make a value table with fields for all possibly necessary types and in options I would make two fields type and valueId, and then I will know the line and column where to read the value in the value table.

Answer 2

• You have the wrong EAV. Value must be in product_option, otherwise the normal form is violated.
  I advise you to look at these slides
  https://www.slideshare.net/billkarwin/practical-ob...
  https://www.slideshare.net/billkarwin/sql-antipatt...