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?

Answers


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.


Need Your Help

Oracle Stored Procedure Call from iSQL PLUS Invalid Identifier

oracle stored-procedures plsql isql

I have created a procedure using the following code using iSQL Plus on Firefox. The procedure compiles successfully.

Why does Expect dump commands without execution?

expect

I'm learning Expect, and I've noticed that my Expect scripts sometimes reach a point where they begin to dump commands without executing them. What causes this to happen? I've scraped Google and a

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.