Notice that p.ProductCode is no longer unique, how do I identify each
Given that Texture has groups of Subtextures and
has to cross join with the product table so that
each combination of texture and subtexture has
its product in its type...
I thought, is that the way to go, concatenate each id from each table??...
The problem is I don't know how to define a unique price for each item
Will you store these compound products in a table? It would be especially useful if not all possible combinations would represent an existing products. Say that you never have a bronze ring, but you can have a bronze doorknob. In this case, there probably would be a table of all existing products. This table would have its own surrogate primary key, and three attributes - foreign keys to the texture, subtexture and products table. Only combinations that represent an actual product would be in this table. Problem solved!
If you don't ever need to represent every such combination in the database, one possible way would be to store these combinations in Java objects which would have attributes for texture id, subtexture id and product id. The equality methods (equals(), hashCode(), compareTo() etc.) would consider all there attributes. If you implement a toString() method to return the representation to be used in the GUI, you can put these objects directly into the combobox. When user chooses one, you read the texture, subtexture and product from it and use it the way you need.
I guess that the first option is what you want, but I can't be sure, of course, since I don't know your entire requirements.
Note: you're using a single term - "product" - in two different meanings. Once as a basic type of product - eg. "necklace" or "ring", and yet again for a completed product - say "golden necklace with subtexture a". This makes not only speaking about it with someone, but also thinking about the entire concept problematic. I'd suggest to devise two different terms for these two meanings.
Joined: Aug 02, 2012
I think your solution is the only way to go, since I have about 20 groups of textures and subtextures, and 10 "products", it is just too tedious to map them out all, thanks