Commodity Table Database Design

Currently want to do a mobile version of the mall, in the design of the database encountered the problem:

such as a dress, this dress has red, white, blue,
Red and x,xl,xll size,
White with S,M,X,XL
Blue with M,X,XL

For their own design of the database has been not very satisfied, look at the way the great God, enlighten.

The extension of the problem of the general shopping malls can not only sell clothes, there will be electronic products, such as a messy post-expansion of how to do?

Reply content:

Currently want to do a mobile version of the mall, in the design of the database encountered the problem:

such as a dress, this dress has red, white, blue,
Red and x,xl,xll size,
White with S,M,X,XL
Blue with M,X,XL

For their own design of the database has been not very satisfied, look at the way the great God, enlighten.

The extension of the problem of the general shopping malls can not only sell clothes, there will be electronic products, such as a messy post-expansion of how to do?

Size (chimas) Table

id  |  mark------------1   |  X2   |  XL3   |  XLL4   |  M5   |  S

Color (colors) Table

id  |  color------------1   |  蓝2   |  白3   |  红4   |  紫5   |  灰

Clothes (Yifus) Table

id  |  name------------1   |  大衣2   |  毛裤3   |  内衣4   |  内裤

Clothing and Color Association (yifu_colors) Table

id  |  color_id  |  yifu_id----------------------------1   |    1       |  12   |    1       |  23   |    2       |  14   |    2       |  2

Clothing and Size association (YIFU_CHIMAS) Table

id  |  chima_id  | yifu_id----------------------------1   |  1         |  12   |  2         |  23   |  1         |  24   |  2         |  1

Depending on your needs, the dress contains three properties of style, color and size. Naturally it is the corresponding four logical data tables.

What you've been wrestling with is the color and size attached to the style.

The first three tables refer to the second floor.

Size (size) Table

id  |  mark------------1   |  X2   |  XL3   |  XLL4   |  M5   |  S

Color (colors) Table

id  |  color------------1   |  蓝2   |  白3   |  红4   |  紫5   |  灰

Clothes (clothes) table

id  |  name------------1   |  大衣2   |  毛裤3   |  内衣4   |  内裤

The fourth table can be a direct correlation table of clothes and sizes and colors.

Clothing style sheet (clothes_spec)

id | clothes_id | size_id | color_id------------------------------------ 1 |      1     |    1    |     2 2 |      2     |    1    |     2 3 |      1     |    3    |     1

The fourth table has the advantage of being clearer, because a table is missing and it is faster to look up. But the problem is that it's more cumbersome to scale up, and each additional attribute adds a column to the table. However, if the volume of goods, the user volume is very large, this approach is still more advantageous.

Extensibility is a problem, give me the idea, wait for Daniel to give a good extensibility plan:

Name

ID (item ID)	(product name)	color (colors)	size (size)	count (Qty)
1	A dress	Red	M	0
1	A dress	Red	X	100
1	A dress	Red	Xl	200
1	A dress	Red	XLL	300

Find out what size red clothes are

select size from table where id={id} and color='red' and count > 0

Suggest you go online to find articles about inventory SKUs and design methods

Product List:

good_id, title, cate_id

Class attribute table:

cate_id, attr_id

Commodity Attribute Classification table:

attr_id, attr_label, attr_key(做alias用), attr_sort1,  颜色, COLOR, 02,  尺码, SIZE, 1

Commodity Attribute Detail Table:

good_id, attr_value_id,attr_id, attr_value_title, attr_value_sort1, 1, 1, 红色, 01, 2, 1, 蓝色, 11, 3, 2, 19, 01, 4, 2, 20, 1

If you want to consider the late extensibility, it is recommended to use the MySQL EVA structure, or the above several to extend the time, are more troublesome.

This design is too bad. SKUs cannot be designed this way.

