您正在尝试对产品变体进行建模，并认为 EAV（实体属性值）可能是必要的。这是您的设计：
PRODUCT -> 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 |
+---------------+您想知道您的设计是否合理，是否会有查询和可扩展性问题，以及是否标准化。
对产品变体进行建模的目标是表示可以具有不同变体的产品，例如尺寸、颜色或材料。每个变体通常都与一个唯一的 SKU（库存单位）相关联。
这是不使用 EAV 的替代设计：
+---------------+ +---------------+
| PRODUCTS |-----< PRODUCT_SKUS |
+---------------+ +---------------+
| #product_id | | #product_id |
| product_name | | #sku_id |
+---------------+ | sku |
| | price |
| +---------------+
| |
+-------^-------+ +------^------+
| OPTIONS |------< SKU_VALUES |
+---------------+ +-------------+
| #product_id | | #product_id |
| #option_id | | #sku_id |
| option_name | | #option_id |
+---------------+ | value_id |
| +------v------+
+-------^-------+ |
| OPTION_VALUES |-------------+
+---------------+
| #product_id |
| #option_id |
| #value_id |
| value_name |
+---------------+主要密钥 (PK)、唯一密钥 (UK) 和外键（外文）：
产品
选项
OPTION_VALUES
PRODUCT_SKUS
SKU_VALUES
使用提供的电子表格中的以下数据：
+-----------+----------+---------+----------+----------+ | ProductId | Product | Size | Color | Class | +-----------+----------+---------+----------+----------+ | 1 | Widget 1 | Small | White | null | | 1 | Widget 1 | Small | Black | null | | 1 | Widget 1 | Large | White | null | | 1 | Widget 1 | Large | Black | null | | 2 | Widget 2 | Small | null | null | | 2 | Widget 2 | Medium | null | null | | 3 | Widget 3 | Medium | null | Amateur | | 3 | Widget 3 | Large | null | Amateur | | 3 | Widget 3 | Medium | null | Professional | | 3 | Widget 3 | Large | null | Professional | +-----------+----------+---------+----------+----------+
数据可以输入到表格如下：
-- PRODUCTS +-----------+----------+ | ProductId | Product | +-----------+----------+ | 1 | Widget 1 | | 2 | Widget 2 | | 3 | Widget 3 | +-----------+----------+ -- OPTIONS +-----------+---------+----------+ | ProductId | OptionId | OptionName | +-----------+---------+----------+ | 1 | 1 | Size | | 1 | 2 | Color | | 2 | 1 | Size | | 3 | 1 | Class | | 3 | 2 | Size | +-----------+---------+----------+ -- OPTION_VALUES +-----------+---------+---------+----------+ | ProductId | OptionId | ValueId | ValueName | +-----------+---------+---------+----------+ | 1 | 1 | 1 | Small | | 1 | 1 | 2 | Large | | 1 | 2 | 1 | White | | 1 | 2 | 2 | Black | | 2 | 1 | 1 | Small | | 2 | 1 | 2 | Medium | | 3 | 1 | 1 | Amateur | | 3 | 1 | 2 | Professional | | 3 | 2 | 1 | Medium | | 3 | 2 | 2 | Large | +-----------+---------+---------+----------+ -- PRODUCT_SKUS +-----------+----------+-----+--------+ | ProductId | SkuId | Sku | Price | +-----------+----------+-----+--------+ | 1 | 1 | W1SSCW | 10.00 | | 1 | 2 | W1SSCB | 10.00 | | 1 | 3 | W1SLCW | 12.00 | | 1 | 4 | W1SLCB | 15.00 | | 2 | 1 | W2SS | 100.00 | | 2 | 2 | W2SM | 100.00 | | 3 | 1 | W3CASM | 50.00 | | 3 | 2 | W3CASL | 50.00 | | 3 | 3 | W3CPSM | 150.00 | | 3 | 4 | W3CPSL | 160.00 | +-----------+----------+-----+--------+ -- SKU_VALUES +-----------+----------+---------+---------+ | ProductId | SkuId | OptionId | ValueId | +-----------+----------+---------+---------+ | 1 | 1 | 1 | 1 | -- W1SSCW: Size; Small | 1 | 1 | 2 | 1 | -- W1SSCW: Color; White | 1 | 2 | 1 | 1 | -- W1SSCB: Size; Small
以上是我的基于 EAV 的产品变体数据库设计是否健全、可扩展且规范化，或者替代关系设计是否提供更好的查询和性能？的详细内容。更多信息请关注PHP中文网其他相关文章！
