How to solve multidimensional data relationship I'm having a problem, that I could solve programmatically, but for my personal (test) project I'm trying to see if I can fix it in the relational model.

Imagine a complex fashion product Pants with these Properties as dimensions:


Length (35,36,37,38)
Size (32,33,34,35)
Color (White, Red, Blue, Yellow, Green)
Kneepatch (Woodcutter, Brown, Silver)
Linked-belt (Brown, Beige, Black)


So there are 4*4*5*3*3 = 720 product variants. I know this is extreme, but I'm purely doing this to immediately test if my system will be able to handle it in a later phase.

So trimmed down I have:

Products

id
name


Properties

id
name


Thinking of RDBMS logic aside I would get a table like this, which of course isn't possible as it would constantly add properties and adds tons of nulls:

Productvariants

 id
 product_id
 property_1_id
 property_2_id
 property_3_id
...etc.


So this is a bit harder than a usual Color/Size combination you normally have on a product.

Now I can fake this methodology and just dump a serialization of Property ID's in one field on the Productvariants, like so:

Productvariants

id
product_id
properties {1, 2, 3}


.. and then split them in my ORM, but I'm afraid this will greatly increase the needed queries in the future (with tens of thousands of product variants) and optimization doesn't seem possible anymore that way.

Does anyone have experience with this and how to maybe intelligently do this? I'm using MySQL.
Products

ProdId
ProdName


Properties

(PropId) (primary key, if required)
ProdId (composite key)
PropCode (composite key)
PropValue (composite key) (if storing a specific value, otherwise you can remove this to simply store the possible properties for any given product)


Basically you store multiple values for a given property that all point back to a single product.  The composite key would need to be unique and you would need to ensure data integrity by either linking PropCode to another table or restricting the values on entry.