How to create a better tables Structure
For my website i have tables
Category :: id | name Product :: id | name | categoryid
Now each category may have different sizes, for that I have also created a table
Size :: id | name | categoryid | price
Now the problem is that each category has also different ingredients that customer can choose to add to his purchased product. And these ingredients have different prices for different sizes. For that I also have a table like
Ingredient :: id | name | sizeid | categoryid | price
I am not sure if this Structure really normalized is. Can someone please help me to optimize this structure and which indexed do i need for this Structure?
Category :: id | name Product :: id | name | categoryid Size :: id | name | Ingredient :: id | name | Category_Size :: id | categoryid | SizeID | Price Category_Ingredient :: id | categoryID | IngredientID | Price
All complex entities are broken into least possible parts (individual table). Then used foreign key relations to relate them logically.
And these ingredients have different prices for different sizes.
If an ingredient's price depends only on its size and not also the category then try this:
Ingredient :: id | name Ingredient_Price :: ingredientid | sizeid | price Ingredient_Category :: ingredientid | categoryid
Structure look good to me. PK will be indexed by defining them as PK of course. You need to add indexes for all FKs:
product.categoryId, size.categoryId, ingredient.categoryId, ingredient.sizeId
Depending on the size of your tables, and on the fact that you might want to display items alphabetically, you might also want to index all the name fields.