Database Design for Product Variants

Question

I want to model product options (not options or attributes, just options).

Thus, each individual option is the product itself. General information that is valid for all product variants is stored in another table (for example: text description). Well, I think this is great and does not need further changes.

For dependent options (for example, color: red, size: small) I created two options.

Option 1:

Brief description of the tables:

• : saves all available parameters (color, size, material, ...)
• value: saves all available values ​​(red, blue, green, small, medium, large, iron, wood).
• option_value: saves all possible combinations of parameters and values ​​(color: {red, blue, green}, size: {small, medium, large}, ...
• product_option_value now connects the product with its parameters (for example: color: red, size: small, product_id: 1; color: blue, size: small, product_id: 1)

Well, I think that this will work very well - on the left side - a description of the metadata (which parameters, which values, which combinations) to create the user interface, - on the right side - the connection with the products.

But there is one problem ... Describes the possible combinations of parameters and values ​​for constructing a graphical user interface and its ability to test it programmatically, but the database cannot be verified.

So I created option 2:

Now I'm not sure that the second solution may be the best. What do you think? Is there room for improvement?

Answer 1

If you want to limit product_option_value only to values ​​that already exist in option_value , then yes, the second model is better.

However, this model allows one value to be used for several parameters (for example, “red” can be both “color” and “size”). I assume that this is not what you wanted, in which case the model should look something like this: