Design mysql table according to needs

Hello everyone, first of all, thank you for browsing this question
Let me talk about the requirements: the attributes of the products in the mall project, for example:
Clothes have two attributes: color and size. My idea is to separate them Let’s create a table to record the price and inventory of different attributes
For example:
Black - size M - 70 yuan - 35 pieces
Black - size L - 73 yuan - 32 pieces
White - size L - 73 yuan - 30 pieces
This table is designed like this, but a problem arises. For example, the mobile phone has several attributes, such as color, memory, operator (non-contract, China Mobile, China Unicom, Telecom)
If still According to the method of clothes above, it is obvious that there is an extra column, that is,
White - 32G - Mobile contract machine - 20 yuan - 10 units
White - 32G - China Unicom contract machine - 30 yuan - 50 units
Gold - 32G - Telecom contract machine - 10 yuan - 40 units

It’s ok if all these attribute information are serialized and stored, but it seems to be a troublesome thing when I want to search for Huawei 32G mobile contract phones. I would like to ask if you have any good ideas to provide, thank you, thank you very much. Thank you
PS: After looking at the design of ecshop, there is a base price for the product, and then the price is added or subtracted from the base price, but I am not very inclined to this approach. It seems a little troublesome when adding products

Reply content:

Hello everyone, first of all, thank you for browsing this question
Let me talk about the requirements: the attributes of the products in the mall project, for example:
Clothes have two attributes: color and size. My idea is to separate them Let’s create a table to record the price and inventory of different attributes
For example:
Black - size M - 70 yuan - 35 pieces
Black - size L - 73 yuan - 32 pieces
White - size L - 73 yuan - 30 pieces
This table is designed like this, but a problem arises. For example, the mobile phone has several attributes, such as color, memory, operator (non-contract, China Mobile, China Unicom, Telecom)
If still According to the method of clothes above, it is obvious that there is an extra column, that is,
White - 32G - Mobile contract machine - 20 yuan - 10 units
White - 32G - China Unicom contract machine - 30 yuan - 50 units
Gold - 32G - Telecom contract machine - 10 yuan - 40 units

It’s ok if all these attribute information are serialized and stored, but it seems to be a troublesome thing when I want to search for Huawei 32G mobile contract phones. I would like to ask if you have any good ideas to provide, thank you, thank you very much. Thank you
PS: After looking at the design of ecshop, there is a base price for the product, and then the price is added or subtracted from the base price, but I am not very inclined to this approach. It seems a little troublesome when adding products

It can be said that the current questions have been asked by others. For your question, you can provide my answer.
https://segmentfault.com/q/1010000005347571/a-1020000005347916
You can design the table as follows:

Product List

商品 id	商品名称
1	iPhone
2	黄瓜

Product attribute table

商品 id	规格	颜色	价格
1	16g	白色	4999
1	32g	白色	5499
2	无	无	1

For your current practice project, this design is most suitable.
However, this design is suitable for products with certain or few attributes . If there are more attributes, then It is necessary to add an attribute field, and some products may not use this attribute, causing waste and causing the attribute table to expand unchanged. For example, product 2 cucumber.
can be changed into the following design:

Attribute table

属性 id	属性名称
1	规格
2	颜色
3	价格

Product category list

种类 id	商品 id
a	1
b	1
c	2

Product attribute table

商品种类 id	属性 id	属性值
a	1	16g
a	2	白色
a	3	4999
b	1	32g
b	2	白色
b	3	4999
c	4	上海

The design is as above, the attribute of the product cucumber will only generate one piece of data.

Personally, I think your question should be divided into two questions. First, how to design a good data structure for the product table, and second, how to better search for products. First of all, the first question: design the product list. First of all, you have to analyze what products your mall business has. The product master table and the product attribute table have a one-to-many relationship. Different products may need to design different relationship structures. A simple idea is like ecshop, which also lists attributes in a separate table, but listing attributes in a single table will cause data redundancy.
1. You can design several more fields in the attribute table.
1). Design a product list. Common fields for storing items.
2). Design a price list. The attribute table fields include specifications and colors. However, since some fields are not universal, there may be redundancy and default values ​​can be placed.
2. How to better search for products.
For example, if you search for: Huawei 32G mobile contract phone, if the user enters Huawei 32G mobile contract phone, you can search for products. If the Huawei 32G mobile contract phone is not a product name, the system should do word segmentation first, and then search and compare it. If Huawei 32G mobile contract machine is directly the product name, you can search directly.

Add an attribute table
product_id key value
Just a few more rows of records for multiple attributes