Thanks for the response. I've been thinking about something sort of like this.
I was planning on producing one product in the products table with a unique product id. That way I can create just one page for all of the variant products.
Then in the Variants table, use the same product for a number of different variants. (Also, I plan on limiting the number of variations to a maximum of 5 types). All of these would be displayed on the 1 product page.
The variants table could look like this for a T-shirt (with Product ID 323 in the Products table):
ID | productID | name1 | value1 | name2 | value2 | name3 | value3 | name4 | value4 | name5 | value5 | quantity | price |
1 | 323 | Color | Black | Size | XL | | | | | | | 3 | 10.99 |
2 | 323 | Color | Black | Size | XXL | | | | | | | 2 | 11.99 |
3 | 323 | Color | Red | Size | XL | | | | | | | 4 | 10.99 |
4 | 323 | Color | Red | Size | XXL | | | | | | | 1 | 11.99 |
Let's say there could be 50 rows for all different combinations of colors and sizes. This would be sort of like a secondary products table, which I could join on my main products table.
What do you think of this table and technique? I would like to plan on making this technique scalable so that it will still be efficient after millions of products.