For this site i've tables
Category :: id | name Product :: id | name | categoryid
Now each category might have different dimensions, for your I've also produced a table
Size :: id | name | categoryid | price
Now however , each category has additionally different elements that customer can pick to increase his bought product. Which elements have different prices for various dimensions. For your I in addition have a table like
Ingredient :: id | name | sizeid | categoryid | price
I don't know if the Structure really stabilized is. Can someone help me to optimize this structure and which indexed will i requirement for this Structure?
Which elements have different prices for various dimensions.
If the ingredient's cost is dependent only on its size and never even the category then do this:
Ingredient :: id | name Ingredient_Price :: ingredientid | sizeid | price Ingredient_Category :: ingredientid | categoryid
Structure look great in my experience. PK is going to be listed in determining them as PK obviously.
You have to add indexes for those FKs:
product.categoryId, size.categoryId, ingredient.categoryId, ingredient.sizeId
With respect to the size your tables, as well as on because you may want to display products alphabetically, you could also wish to index all of the title fields.
Category :: id title
Product :: id title categoryid
Size :: id title
Component :: id title
Category_Size :: id categoryid SizeID Cost
Category_Component :: id categoryID IngredientID Cost
All complex organizations are damaged into least possible parts (individual table). Then used foreign key relations to relate them realistically.