How to create multiple related tables in mysql database -

my title might not convey i'm trying achieve, have tables related can't figure out how create relationship make storing , retrieving data easy. below able come with.

color table colorid(pk) productid(fk) colorname   size table sizeid(pk) productid(fk) size   product table productid(pk) priceid(fk) qty name title  price table priceid(pk) productid(fk) 

now problem have product different variety example men's blue addidas glide running shoes productid 1, product have different sizes , different color , prices varies on sizes , color. example, if pair of shoe color blue size 11 sells $50, same pair of shoe bigger size , different color size 12 color red might sell $55, same pair of shoe color blue might have size 11 available in stock , color blue size 12 of pair might not available, how create table save differences between color , sizes, , prices. on this, thanks

you shouldnt have every single attribute in it's own table, potentially better way have product table , variant table. way have price, size, color, quantity available, etc in same table, join on product i.e. men's blue adidas glide running shoes. variant table this:

| variant_id | product_id | price | colour_id | size_id | quantity_available | in_stock | |          1 |          1 | 50.00 |         1 |     11  |                 20 |        1 | |          2 |          1 | 55.00 |         2 |     12  |                  0 |        1 | 

then load each variant individually, , have quantity_available updated whenever make sale. i've included in_stock boolean can override whether stock appears on site without having adjust quantity_available. you'd have variant_id on purchase table can join these later. also, having sizes , colours on own tables can put taxonomy in place aggregate blues say, or xs, s, m, l, xl.

something like:

| colour_id | name          | base_colour_id | |         1 | royal blue    |              3 | |         2 | spanish blue  |              3 | |         3 | blue          |              3 | |         4 | ultramarine   |              3 | |         5 | green         |              5 | |         6 | mint green    |              5 | 

this way can add more unique colour variants , still report on base colours or full colour names. here you'd use: select * variant_colour join variant_colour b on b.colour_id = a.base_colour_id you'd get:

| colour_id | name          | base_colour_id | colour_id | name          | base_colour_id | |         1 | royal blue    |              3 |         3 | blue          |              3 | |         2 | spanish blue  |              3 |         3 | blue          |              3 | |         3 | blue          |              3 |         3 | blue          |              3 | |         4 | ultramarine   |              3 |         3 | blue          |              3 | |         5 | green         |              5 |         5 | green         |              5 | |         6 | mint green    |              5 |         5 | green         |              5 | 

this same idea can used sizes etc don't end hundreds of unique attribute variants make info impossible report on.