Is my EAV-based product variant database design sound, scalable, and normalized, or does the alternative relational design offer better querying and performance?

Modeling Product Variants

Problem

You are trying to model product variants and believe EAV (Entity-Attribute-Value) might be necessary. Here's your design:

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   |
 +---------------+

You want to know if your design is sound, whether it will have querying and scalability issues, and if it's normalized.

Design Consideration

The goal of modeling product variants is to represent products that can have different variations, such as size, color, or material. Each variation is typically associated with a unique SKU (Stock Keeping Unit).

Here's an alternative design that does not use 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   |
+---------------+

Primary Keys (PKs), Unique Keys (UKs), and Foreign Keys (FKs):

• PRODUCTS

  • PK: product_id
  • UK: product_name

• OPTIONS

  • PK: product_id, option_id
  • UK: product_id, option_name

• OPTION_VALUES

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

• PRODUCT_SKUS

  • PK: product_id, sku_id
  • UK: sku_id
  • FK: product_id references PRODUCTS(product_id)

• SKU_VALUES

  • PK: product_id, sku_id, option_id
  • FK: (product_id, sku_id) references PRODUCT_SKUS(product_id, sku_id)
  • FK: (product_id, option_id) references OPTIONS(product_id, option_id)
  • FK: (product_id, option_id, value_id) references OPTION_VALUES(product_id, option_id, value_id)

Data Example

Using the following data from the spreadsheet provided:

+-----------+----------+---------+----------+----------+
| 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 |
+-----------+----------+---------+----------+----------+

The data can be entered into the tables as follows:

-- 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

The above is the detailed content of Is my EAV-based product variant database design sound, scalable, and normalized, or does the alternative relational design offer better querying and performance?. For more information, please follow other related articles on the PHP Chinese website!