Database design suggestions for a configurable product eshop

Question

Hello,

I am biulding an e-shop that will have configurable products. The configurable parts will need to have different prices and stocks from the main product. What database design would be best in this case?

I started with something like this.

Features

• id
• name

Features Options

• id
• id_feature
• value

Products

• id
• name
• price

Products Features

• id
• id_product
• id_feature
• value ( save the value from the feature-options for ease in search )
• configurable (yes, no)

The problem is that now I am stuck on how to save the configurable product features. I was thinking of saving their value as a json. But that will make saving price modification for a certain option difficult.

How would you go about this ?

Thank you.

Answer 1

Two more tables will do the magic (below). Hacks like JSON or parseable text fields can and should always be avoided.

  -- one product <id_product> can have multiple
  -- product variants <id> with different features  enabled
  product_variant
    id
    id_product (one product - many variants)

  -- features, enabled for certain <id_product_variant>
  product_variant_enabled_feature
    id
    id_product_variant (one variant - many features)
    id_product_feature
    unique_constraint(id_product_variant, id_product_feature)