MySQL database to store product, color, size and stock

Question

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?

Answer 1

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.