Products , variants and stocks - what is the best way to design database

Question

This is a t-shirt store and i have these variants :

For example:

T-shirt 1 - Available Colors : Red , Blue , White | Available Sizes : S , M , L

T-shirt 2 - Available Colors : Green , White | Available Sizes : S , M , L , XL

and so on...

And i dont want to use Eav Model. Im trying to solve stock problem!

this is my tables

products:

products:
 ------------------
|  id  |    name   |
 ------------------
|  1   |  t-shirt  | 
 ------------------

options:
 ------------------------------
|  id  |  product_id  |  name  |
 ------------------------------
|  1   |      1       |  size  |
 ------------------------------
|  2   |      1       |  color |
 ----------------------------

option_values:
 ---------------------------------------------
|  id  |  product_id  |  option_id  |  name  |
---------------------------------------------
|  1   |      1       |      1       |  L |
---------------------------------------------
|  2   |      1       |      1       |  M |
----------------------------------------------
|  3   |      1       |      2       |  red |
---------------------------------------------
|  4   |      1       |      2       |  blue |
----------------------------------------------

How can i keep stock of every variation . I coulndt solve it !

And one more question : Iam using laravel 4.2 what is the true way of INSERTing this values

i should create product from admin panel ,

after fill all blanks like , name , price , variants and so on

how can insert these all datas to different tables with a submit button

should i make queries for each variant ?

Answer 1

Each colour/size could be an individual 'child' product in the product table and then have a parent item of t-shirt, as opposed to having 3 separate tables. It allows you to manage stock separately and have separate prices for each option.

A possible way of doing it?

---------------------------------------------------------------
|    sku    |        name       |  qty  | price |   parent    |
---------------------------------------------------------------
|   TS001   |      t-shirt 1    |   1   | 25.00 |     0       |
---------------------------------------------------------------
|  TS001-L  |  t-shirt 1 Large  |  100  | 26.00 |   TS001     |
---------------------------------------------------------------