I'm building products database where a product may have multiple variants such as Size, Color, Shape, etc. for a single product. Tricky part I have trouble with is structure of the database allowing me to link the dependencies e.g. "Red" "M" "T-shirt" or "Blue" "XS" "Trousers" which in this case are variants color and size together. I want those variants to be generic so, I'm avoiding sizes, shapes, colors tables etc. Some items may not even have any variant at all or have just one e.g. just the color.
Ideally the shape would look like so
products
| id | name |
|---|---|
| 1 | T-shirt |
| 2 | Trousers |
variants
| id | name |
|---|---|
| 1 | Color |
| 2 | Size |
variant_values
| id | variant_id | name |
|---|---|---|
| 1 | 1 | Blue |
| 2 | 1 | Red |
| 3 | 2 | XS |
| 4 | 2 | S |
| 5 | 2 | M |
product_variants
I probably don’t even need variant_id here if I provide variant_value_id which already has relationship with a specific variant.
| id | product_id | variant_id | variant_value_id |
|---|---|---|---|
| 1 | 1 | 1 | 1 |
The product_variants structure allows me to have "Blue T-shirt" record but how do I have those variant_value_ids work together so I could store a Blue M T-shirt or Red XS Trousers etc?
I have inspired my model from those threads...
...but could not find an answer that would apply to my case where I could combine the variants together.
