How to solve the problem of types in relation many-to-many?

The screenshot shows an example of when many-to-many. The products may be different parameters, and the parameters can be different products.


Can't figure out how to solve the problem types, if the table options field value can be of different types? For example, I have a record where value TEXT(2000), respectively, this field will not fit in the current VARCHAR(255).

The only thing that came to mind to make a separate table with fields product_id (INT), value (TEXT). But something I do not particularly like that decision.

Has anyone encountered similar problems?
June 10th 19 at 15:59
2 answers
June 10th 19 at 16:01
It all depends on how this value You are going to use.

If you will not have to do the search index, you can do MAX, and it is to store a value type, something like {"$type":"int","value":3}

If it will still have to do the selection, I probably would have made the value table with fields for all possible types of needs and options made two fields type and valueId well, then I'll know the line and column where to read the value in the table value.
Regarding 1st option: Bad, due to the fact that all the fields will be too "heavy". Or am I wrong? For example, the data type JSON or TEXT is not fixed length? That is



{"$type":"text","value":"some text"}

Will take a different amount of memory or fixed as CHAR?

About 2nd option, thought only in the form of separate tables for each type. - Obie.Strom commented on June 10th 19 at 16:04
char will always be a fixed length, regardless of the size of the string, and varchar here, even if you declare 255, and put a string of 10 characters, then it will take 11 bytes, assuming single-byte encoding (lat1), well, if dubita, it will be 21 bytes to occupy.

In General, if You are going to store in a value the texts of indefinite size, make varchar(2000) for example and memory will take the place of length + 1 bytes. char takes place when values of the same length (IDs, flags) You will save memory by 1 byte with value, but it will not be faster compared to varchar. But varchar will be faster than text because of the tog, which is varchar in the table is stored, and the text is stored outside and the table only link to the storage location. - Abner32 commented on June 10th 19 at 16:07
June 10th 19 at 16:03
  • You have the wrong EAV. Value must be in product_option otherwise interferes with the normal form.
  • EAV is generally a so-so pattern, even it is often called an anti-pattern, and not just because of type issues, but because of problems with filtering on multiple conditions.
  • The types of EAV decide who is in that much. Someone separate tables, someone separate field indicating the type (and a large varchar value), someone just text.

Suggest you look at these slides
If value will be product_option don't succeed regard one-to-many, which will lead to duplication of records in the table. - Obie.Strom commented on June 10th 19 at 16:06
Records in product_option you and so more than one will be on the product. But in your circuit is obtained and the duplication of option names. Read about EAV. - Abner32 commented on June 10th 19 at 16:09

Find more questions by tags DatabasesDatabase designMySQL