Hi!
I am a student and I am working on database that concerns customer-order-product relationship. When designing it I encountered an issue that I do not really know how to solve it in the way it seems reasonable and obeys Normalization process. I would appreciate for any assistance.
Description of my problem:
We have product for instance T-shirt that can have different sizes and colors and each of this specific combination has quantity (http://img251.imageshack.us/img251/8871/products.jpg)
Next I created relation between Orders and Products as many-to-many (http://img406.imageshack.us/img406/8471/orderdetails.jpg). And here problems starts… In Order_Details table if someone order 5x of T-shirt with size S and color blue, and then 3x of the same T-shirt but with M size I will have repeated some values. Another thing is that Size and Color attributes rely only only on ProductFK Key and not the whole Key (OrderFK-ProductFK) so I moved these details to another table like here(http://img18.imageshack.us/img18/3507/productorderdetails.jpg), but now arise another problem Quantity attribute… in my it should be placed in Order_Details, right ? The thing is now I ended up with basically the same table like Product_Size_Color (http://img251.imageshack.us/img251/8871/products.jpg) but without Quantity attribute which we do not need here since I assume it should be in Order_Details table. How I can combine it altogether to get consistent database ?
Whole database until now looks like this (http://img18.imageshack.us/img18/8130/wholedatabase.jpg)
If there is anything else that in Your opinion is bad please let me know.
