MySQL数据库存储产品，颜色，大小和库存 [英] MySQL database to store product, color, size and stock
问题描述
让我们来说，把下面的衬衫储存到db：
产品名称：Nike shirt
可选颜色：黑色，白色，蓝色
尺寸：M，L，XL
库存：黑色 - M - 5 pc
白色 - L - 10 pc
蓝色 - M - 2 pc
蓝色 - XL - 3 pc
（等等...）
而不是像这样在一个表中迭代地存储上面的信息：
表衬衫
id产品颜色大小股票
---------------------------------------------
1耐克衬衫黑色M 5
2耐克衬衫白色L 10
3耐克衬衫蓝色M 2
4耐克衬衫蓝色XL 3
....
有效设计表格以保持这些属性和产品的最佳方法是什么？
我知道可以将多个表加在一起，但是我需要关于这些属性的建议，关于如何将差分表单独分开，并在人们访问相应的页面并显示多少个库存时获取信息留给特定尺寸？
提前感谢。
这是你的桌子。
衬衫
id产品颜色尺寸库存
--- ------------------------------------------
1耐克衬衫黑色M 5
2耐克衬衫白色L 10
3耐克衬衫蓝色M 2
4耐克衬衫蓝色XL 3
....
您可以看到如何重复产品名称Nike Shirt和颜色blue。在规范化关系数据库中，我们不想复制任何信息。如果有人在第4行意外地将耐克衬衫更改为耐克裙，会发生什么情况？
所以，让我们规范化您的表格。
我们将从Product表开始。 p>
产品
id产品
------ ------- -----
0 Nike Shirt
通常，数据库ID号以零开头，不是一个。
接下来，让我们创建一个颜色表。
颜色
id颜色
------ -------
0黑色
1白色
2蓝色
接下来，我们来创建一个Size表。
大小
id size
------ -----
0 XS
1 S
2 M
3 L
4 XL
5 XXL
现在我们有3个单独的对象表。我们如何将它们放在一起，以便我们可以看到有什么？
您的原始表格具有正确的想法。
库存
id产品颜色尺寸库存
------------------- --------------------------
0 0 0 2 5
1 0 1 3 10
2 0 2 2 2
3 0 2 4 3
产品，颜色和尺寸数字外键返回到产品，颜色和尺寸表。我们这样做的原因是消除信息的重复。您可以看到，任何信息都存储在一个地方，只存储在一个地方。
库存表中不需要该ID。产品，颜色和尺寸应该是唯一的，所以这3个字段可以在库存表中复合键。
在实际的零售店中，产品可能有许多不同的属性。属性可能会存储在键/值表中。对于您的简单表，我们可以将表格分解为标准化关系表。
希望对您有所帮助。
I am very new in php and mysql, I have an assignment for shopping cart dealing with shirt store, and was confusing with database design in storing shirt attributes such as color, size and stock for each item.
Let's say to store below shirt to db:
Product name: Nike shirt
Available colors: black, white, blue
Size: M, L, XL
Stock: Black - M - 5 pc
White - L - 10 pc
Blue - M - 2 pc
Blue - XL - 3 pc
(and so on...)
Instead of storing above info iteratively in a table like so:
table shirt
id product color size stock
---------------------------------------------
1 Nike Shirt black M 5
2 Nike Shirt white L 10
3 Nike Shirt blue M 2
4 Nike Shirt blue XL 3
....
What is the best way to design table to keep these attribute and product effectively?
I know that could be JOIN multiple table together, but I need advise on these attributes on how to put separately with difference table and fetch the info when people goes to respective page and show them up how many stock are left for the specific size?
Thanks in advance.
Here's your table.
Shirt
id product color size stock
---------------------------------------------
1 Nike Shirt black M 5
2 Nike Shirt white L 10
3 Nike Shirt blue M 2
4 Nike Shirt blue XL 3
....
You see how you've duplicated the product name "Nike Shirt" and the color "blue". In a normalized relational database, we don't want to duplicate any information. What do you think would happen if someone accidently changed "Nike Shirt" to "Nike Skirt" in row 4?
So, let's normalize your table.
We'll start with a Product table.
Product
id product
------ ------------
0 Nike Shirt
Generally, database id numbers start with zero, not one.
Next, let's create a Color table.
Color
id color
------ -------
0 black
1 white
2 blue
Next, let's create a Size table.
Size
id size
------ -----
0 XS
1 S
2 M
3 L
4 XL
5 XXL
Ok, now we have 3 separate object tables. How do we put them together so we can see what's in stock?
You had the right idea with your original table.
Stock
id product color size stock
---------------------------------------------
0 0 0 2 5
1 0 1 3 10
2 0 2 2 2
3 0 2 4 3
The product, color, and size numbers are foreign keys back to the Product, Color, and Size tables. The reason we do this is to eliminate duplication of the information. You can see that any piece of information is stored in one place and one place only.
The id isn't necessary on the Stock table. The product, color, and size should be unique, so those 3 fields could make a compound key to the Stock table.
In an actual retail store, a product could have many different attributes. The attributes would probably be stored in a key/value table. For your simple table, we can break the table up into normalized relational tables.
I hope this is helpful to you.
这篇关于MySQL数据库存储产品，颜色，大小和库存的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
