MySQL database for storing product, color, size and stock
I am very new to php and mysql, I have an assignment for a shirt shop cart and is confused by the database design when storing shirt attributes like color, size and stock for each item.
Let's say to store the undershirt up 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 the above information iteratively in a table like:
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 a table to use these attributes and product effectively?
I know it is possible to concatenate multiple tables together, but I need to communicate these attributes on how to separate the diff table and retrieve information when people go to the corresponding page and show how much stock is left for a specific size?
Thanks in advance.
source to share
Here is 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 can see how you 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 happens if someone accidentally changes "Nike Shirt" to "Nike Skirt" on line 4?
So let's normalize your table.
We'll start with the Product table.
Product
id product
------ ------------
0 Nike Shirt
Typically, database ID numbers start with zero, not one.
Then create a color table.
Color
id color
------ -------
0 black
1 white
2 blue
Then create a size chart.
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
Product, color and size numbers are foreign keys for product, color and size tables. The reason we do this is to eliminate duplicate information. You can see that any information is stored in one place and only in one place.
ID is not required in the inventory table. Product, color and size must be unique, so these 3 fields can make a composite key in the inventory table.
In an actual retail store, a product can have many different attributes. The attributes will probably be stored in a key / value table . For your simple table, we can split the table into normalized relational tables.
Hope this helps you.
source to share