How can a normalized database design effectively model product variants as an alternative to the Entity-Attribute-Value (EAV) model?

Modeling Product Variants

You're trying to model product variants and had some concerns about using EAV (Entity-Attribute-Value). Here's an alternative design you could consider:

Normalized Design

The following design normalizes the data structure for product variants:

+---------------+     +-----------------+
| 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, Unique, 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)

How it Works

• PRODUCTS contains basic product information like name.
• OPTIONS lists the available options, such as Size or Color.
• OPTION_VALUES holds the specific values of options, such as Small or Red.
• PRODUCT_OPTIONS maps which options are associated with products.
• PRODUCT_VARIANTS stores the actual product variants along with their SKUs.
• VARIANT_VALUES links variants to their option values.

This design allows you to define the options and their values independently, making it flexible to add new options or values in the future.

Sample Data

Here's an example of how you could enter data in these tables based on the spreadsheet in your question:

PRODUCTS
========
product_id product_name
---------- ------------
1          Widget 1
2          Widget 2
3          Widget 3

OPTIONS
=======
option_id option_name
--------- -----------
1         Size SL
2         Color
3         Size SM
4         Class
5         Size ML

OPTION_VALUES
=============
option_id value_id value_name
--------- -------- ------------
1         1        Small        (Size SL)
1         2        Large        (Size SL)
2         1        White        (Color)
2         2        Black        (Color)
3         1        Small        (Size SM)
3         2        Medium       (Size SM)
4         1        Amateur      (Class)
4         2        Professional (Class)
5         1        Medium       (Size ML)
5         2        Large        (Size ML)

PRODUCT_OPTIONS
===============
product_id option_id
---------- ---------
1          1         (Widget 1; Size SL)
1          2         (Widget 1; Color)
2          3         (Widget 2; Size SM)
3          4         (Widget 3; Class)
3          5         (Widget 4; Size ML)

PRODUCT_VARIANTS
================
product_id variant_id sku_id
---------- ---------- ------
1          1          W1SSCW (Widget 1)
1          2          W1SSCB (Widget 1)
1          3          W1SLCW (Widget 1)
1          4          W1SLCB (Widget 1)
2          1          W2SS   (Widget 2)
2          2          W2SM   (Widget 2)
3          1          W3CASM (Widget 3)
3          2          W3CASL (Widget 3)
3          3          W3CPSM (Widget 3)
3          4          W3CPSL (Widget 3)

VARIANT_VALUES
==============
product_id variant_id option_id value_id
---------- ---------- --------- --------
1          1          1         1        (W1SSCW; Size SL; Small)
1          1          2         1        (W1SSCW; Color; White)
1          2          1         1        (W1SSCB; Size SL; Small)
1          2          2         2        (W1SSCB; Color; Black)
1          3          1         2        (W1SLCW; Size SL; Large)
1          3          2         1        (W1SLCW; Color; White)
1          4          1         2        (W1SLCB; Size SL; Large)
1          4          2         2        (W1SLCB; Color; Black)
2          1          3         1        (W2SS; Size SM; Small)
2          2          3         2        (W2SM; Size SM; Medium)
3          1          4         1        (W3CASM; Class; Amateur)
3          1          5         1        (W3CASM; Size ML; Medium)
3          2          4         1        (W3CASL; Class; Amateur)
3          2          5         2        (W3CASL; Size ML; Large)
3          3          4         2        (W3CPSM; Class; Professional)
3          3          5         1        (W3CPSM; Size ML; Medium)
3          4          4         2        (W3CPSL; Class; Professional)
3          4          5         2        (W3CPSL; Size ML; Large)

Advantages

• Provides greater flexibility and scalability.
• Simplifies querying by avoiding complex joins.
• Enforces data integrity through foreign keys.

Disadvantages

• Requires more tables compared to EAV.
• May involve more complex database design and maintenance.

Conclusion

This normalized design is a viable alternative to EAV for modeling product variants. It offers flexibility, scalability, and data integrity while also being relatively easy to query. However, the specific choice between EAV and normalization should be made based on the specific requirements and trade-offs of your application.

The above is the detailed content of How can a normalized database design effectively model product variants as an alternative to the Entity-Attribute-Value (EAV) model?. For more information, please follow other related articles on the PHP Chinese website!