Database normalization for shoes online shop

Question

UPDATE

Here's my current database diagram, for online shoes that would sell

• shoes that will have different categories(heels, wedges, sandals, heels discount, etc)
• each shoes will have different color, different size. for each color and size,
• it will have different stock and maybe have different order type(Ready stock / Pre order)
• for each size and color, maybe it will have different price
• and each shoes with different size and color, they will(maybe) have different image rather than the generic ones

am i doing it correctly? what should i do to improve this database? i really really appreciate any feedback/input/critics

END UPDATE

im newbie in database design and confused with database design for an online shop(MySQL database)

Say that the online shop will sell shoes with different color, size, different stock and price for each color and size.

i came up with these tables:

generic_shoes

id
name
category_id
details
image

colors

id
color

sizes

id
size
size_details

specific_shoes

id
generic_shoes_id //references to generic_shoes
name             // if the owner wants to make another name, if not then use generic_shoes' name
order_type       //ready stock or pre order

then i come up with pivot colors_shoes and sizes_shoes with many to many rel: colors_specific_shoes

id
color_id
specific_shoes_id

sizes_specific_shoes

id
size_id
specific_shoes_id

then im thinking that diff color and size will have diff stock and maybe different price, so i come up with:

shoes_product_details

id                //will be used in the orders table
colors_shoes_id   //reference to pivot colors_shoes
sizes_shoes_id    //reference to pivot sizes_shoes
specific_shoes_id //reference to specific_shoes
stock             //each color and size will have diff stock
price             //each color and size maybe will have diff price
weight            //each size will have different weight

i tought these tables would do just fine, but then im thinking that, should i use this table instead?

remove :

1. sizes_specific_shoes
2. colors_specific_shoes

and just use the color id and size id in the specific_shoes_details table: (in the table below, i just reference directly to colors and sizes table)

specific_product_details table

id                      //will be used in the orders table
color_id                //reference to colors table
size_id                 //reference to sizes table
specific_shoes_id       //reference to specific_shoes
stock                   //each color and size will have diff stock
price                   //each color and size maybe will have diff price
weight                  //each size will have different weight

i think the problem is that with the last table is -> foreign key cascade if the color/size deleted then it will also delete the specific_shoes_details row.

anyone can give me better approach? because i dont know which method should i use, the first one with color and size pivot table or the last one..

thanks

i would really appreciate any help/feedback

Answer 1

I think you did a good job with the database design.

I would go with your last one. There is little chance that you will ever want to delete a color or size. You're not longer going to do business in size 18?

A better option to deleting would be to set the color to be inactive. Add an active column so you can turn it off, but still keep inventory.

I would also add description and/or images to specific shoes.

I believe you can also choose to not cascade on delete if that's all you're concerned about.