How to store the properties of the goods in mysql?

Question

Now I'm planning the structure of the database products. There was a question of storage options / parameters of the goods. For example, if it were the size of the goods - it would be enough to create three fields to the width / height / depth, but what if dozens of parameters? It is assumed that the list of parameters can be changed and I see about this structure: 1 - width 2 - height ... 11 - color

but I can not tie it like a commodity - it is possible to make the field with json type

{1: "20", 2 "30", ..., 11: "white"} and then treated at a sample of the goods, but I do not know how then do filter parameters

who can advise on the right path?

Answer 1

This thing can be achieved with three methods

1. Adding all separate columns in the products table.
2. Adding an extras column with longtext type at end of products table to store all extra/dynamic parameters in it by json-encoding or serializing.

3. Last one is to use a separate meta table for storing key,value pair alongwith product_id in that table.

   CREATE TABLE product_meta()
       id BIGINT AUTO_INCREMENT PRIMARY KEY,  // Primary key of this table
       product_id INT, // For joining purpose and reference
       meta_key VARCHAR(16),
       meta_value VARCHAR(64)
   );
   

So then for product ID 137, you might have several records:

|------------------------------------------|
| id   | product_id | meta_key | meta_value|
|------------------------------------------|
| 3137 | 137        | size     | large     |
| 3138 | 137        | color    | green     |
| 3139 | 137        | height   | 11.3      |
|------------------------------------------|