How to Efficiently Model Product Variants in a Database Design?

Modeling Product Variants

In a database design, products can have variants such as size, color, etc. To efficiently manage such product variants, consider using the following approach:

Design:

--------------- ---------------
| PRODUCTS |-----< PRODUCT_VARIANTS |
--------------- ---------------
| #product_id | | #product_id |
| product_name | | #variant_id |
--------------- | sku_id |

    |             +---------------+
    |                       |

--------^-------- --------^--------
| PRODUCT_OPTIONS |-----< VARIANT_VALUES |
----------------- -----------------
| #product_id | | #product_id |
| #option_id | | #variant_id |
--------v-------- | #option_id |

    |              |  value_id       |

----------------- --------v--------
| OPTIONS | |
--------------- |
| #option_id | |
| option_name | |
--------------- |

    |                       |

-------^------- |
| OPTION_VALUES |---------------
---------------
| #option_id |
| #value_id |
| value_name |
---------------

Primary Keys, Unique Keys, and Foreign Keys:

• PRODUCTS

  • PK: product_id
  • UK: product_name

• OPTIONS

  • PK: option_id
  • UK: option_name

• OPTION_VALUES

  • PK: option_id, value_id
  • UK: option_id, value_name
  • FK: option_id REFERENCES OPTIONS (option_id)

• PRODUCT_OPTIONS

  • PK: product_id, option_id
  • FK: product_id REFERENCES PRODUCTS (product_id)
  • FK: option_id REFERENCES OPTIONS (option_id)

• PRODUCT_VARIANTS

  • PK: product_id, variant_id
  • UK: sku_id
  • FK: product_id REFERENCES PRODUCTS (product_id)

• VARIANT_VALUES

  • PK: product_id, variant_id, option_id
  • FK: product_id, variant_id REFERENCES PRODUCT_VARIANTS (product_id, variant_id)
  • FK: product_id, option_id REFERENCES PRODUCT_OPTIONS (product_id, option_id)
  • FK: option_id, value_id REFERENCES OPTION_VALUES (option_id, value_Id)

Example Data:

PRODUCTS	PRODUCT_VARIANTS	PRODUCT_OPTIONS	OPTIONS	OPTION_VALUES	VARIANT_VALUES
1, Shirt	1, Size	1, Shirt, Size	1, Size	1, Small	1, Small
1, Shirt	2, Color	1, Shirt, Color	2, Color	1, White	2, White
2, Trousers	3, Size	2, Trousers, Size	1, Size	2, Medium	3, Medium
3, Trousers	4, Color	2, Trousers, Color	2, Color	1, White	4, White

Design Considerations:

• This design allows the addition of new products and variants dynamically.
• Every variant is represented by a unique SKU.
• SKU values are unique across all variants.
• The design ensures that each product variant combination is associated with a unique SKU and set of options and values.
• Variant options can be added or removed without affecting existing variants.

Example Use Case:

Consider a Shirt product that can have different sizes and colors. Using this design, you can create the product Shirt, the variant Size with Small and Medium values, and the variant Color with Red and Blue values. Each combination of size and color will have a unique SKU, such as Small Red or Medium Blue.

The above is the detailed content of How to Efficiently Model Product Variants in a Database Design?. For more information, please follow other related articles on the PHP Chinese website!