您正在嘗試對產品變體進行建模，並認為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)與外鍵（外文）：
產品
+-----------+----------+---------+----------+----------+ | 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中文網其他相關文章！
