What's the best way to store my datastructure in database?

I am building an online shop and I have a problem. I will have products which have a direct price (for example HTC Touch 2 Smartphone: $299.00 ), but in the same time I will have products which have prices for combinations based on varieties: for example:

product: Nike Exclusive T-Shirt 2011
varieties: 
   Sizes: L, XL
   Colors: red, blue
combinations will be: 
L white - $10
XL white - $15
L blue - $11
XL blue - $16

I need the best way (or the only one working :) ) of database structure, where I can store both types of products, and if I want to list my products from a category which contains both types of products on the webpage (single price, multiple prices) i can build a mysql query to get all the products in the same query.

thanks

UPDATE

I will have for sure the following tables: [products], [varieties] (color, size), [varietyValues] (which stores what king od colors and what kind of sizes does the product have - each one is a row in the table {productId + varietyId + value (red, S, M, green, XL, etc...)} ). After this one I would have another table [combinations], with a many-to-many [n-to-m] relationship between [combinations] and [varietyValues] which will result a new table [combPrices]. Each row of this new n-to-m table will have a price.

Now the problem is I can't figure out how to store single-price products in this data structure.

---

UPDATE 2

In this image you开发者_开发问答 can see the database diagram, which I think would be ok for the multiple-price products:

THE MAIN PROBLEM: Since this is a webshop, people will put items in the shopping cart. I think the items inserted into the shopping cart should be from the same table (in our case it would be the [combinations] table, since there are the prices stored).

Here are some data for these tables, just to be more clear:

[products]

productid   |   productName
1           |   Nike T-Shirt
2           |   HTC Touch 2 Smartphone

[specifications]

specId   |   productId   |   specName
1        |   1           |   Size
2        |   1           |   Color

[specvalues]

specValueId   |   specId   |   svValue
1             |   1        |   L
2             |   1        |   XL
3             |   2        |   white
4             |   2        |   blue
5             |   2        |   red

[combinations] (items into the cart)

combinationId   |   price   |   description
1               |   10      |   White L Nike T-Shirt
2               |   15      |   White XL Nike T-Shirt
3               |   11      |   Blue L Nike T-Shirt
4               |   16      |   Blue XL Nike T-Shirt
5               |   18      |   Red XL Nike T-Shirt

[combinationParts]

nmid   |   combinationId   |   specValueId
1      |   1               |   1
2      |   1               |   3
3      |   2               |   2
4      |   2               |   3
5      |   3               |   1
1      |   3               |   4
2      |   4               |   2
3      |   4               |   4
4      |   5               |   2
5      |   5               |   5

I hope my diagram and database population does make sense :) .

So the final question is how can I store the single price products (HTC Touch 2 Smartphone) so it can be added to shopping cart just like the multiple price products.

---

You could easily store the price next to the product and use a query like this to get the products.

SELECT p.productid, 
       p.productname, 
       CASE 
         WHEN cb.combinationid > 0 THEN cb.price 
         ELSE p.price
       END, 
       cb.combinationid 
FROM   products p 
       LEFT JOIN specifications sp 
         ON sp.productid = p.productid 
       LEFT JOIN specvalues spv 
         ON spv.specid = sp.specid 
       LEFT JOIN combinationparts cbp 
         ON cbp.specvalueid = spv.specvalueid 
       LEFT JOIN combinations cb 
         ON cb.combinationid = cbp.combinationid 
WHERE  p.productid IN ( 1, 2 ) 
       AND CASE 
             WHEN cb.combinationid > 0 THEN cb.combinationid IN ( -100, 1, 2 ) 
             ELSE 1 = 1 
           END 

this needs as input product numbers and combinations. Since the combinations can be missing, I've added -100 as default value

---

Why don't you just add a nullable price column to your products table ?

If you want to easily access all the prices with a single query, you could use a view :

create view combPrices as
select ProductID, null as Variety, Price from tbProducts where Price is not null
union
select tbProductDesc.ProductID, tbProductDesc.Variety, tbProductDesc.Price 
from tbProducts
inner join tbProductDesc on tbProducts.ProductID = tbProductDesc.ProductID

---

Hmm, it seems like you're going to need two tables with a one to many relationship.
Something like: (Psudocode)

Products
   ProductID int autoincrement
   ProductName text

ProductDesc
   ProductDescID int autoincrement
   ProductID int foreign_key
   ProductDescription text
   Price float

So you would then fill it like:

Products
   1 'HTC Touch 2'
   2 'White Shirt'
   3 'Blue Shirt'
   4 'Nike Exclusive T-Shirt 2011'

ProductDesc
   1 1 'Smartphone' 299.00
   2 2 'Large' 10.00
   3 2 'XL' 15.00
   4 3 'Large' 11.00
   5 3 'XL' 16.00
   6 4 'L White' 10.00 
   7 4 'XL White' 15.00
   8 4 'L blue' 11.00
   9 4 'XL blue' 16.00

Then it wouldn't matter if a product had one price or many prices.