product optnios and variants
Magento allows customizing a product before adding the product to the cart. Customization happens through the customizable options defined by the merchant. Options can represent customization, other products, digital goods so on.
All Magento product types represented through the options.
- ProductOption - represents a product characteristic which allows editing before adding to cart. ProductOption has to have a label, information on how option values should be displayed, and is this option mandatory.
- ProductOptionValues - belong to a particular option as to a group and represent selections which allowed by the option. Option value could by display label, also one or may values could be pre-selected.
- ProductVariant - represent the final selection of one or multiple option values that will characterize the customized product in a shopping cart. Depends on the business scenario, a particular product variant could be linked with an existing product, with a price, or an inventory record, or no be linked to any. Even with no entities associated with the variant, it still has great value for a catalog because the presence of the variant says that such composition of options and their values described by the variant makes sense so that it can be purchased.
The application distinguishes two approaches to manage options:
Approach 1, "One to many": Multiple options selections lead a shopper to a selection of the single variation.
Example: configurable products
Approach 2, "One to one": The selection of multiple options leads to multiple product variants.
Examples: bundle product, customizable product, downloadable products.
Example: configurable product with customizable option.
During my research, I analyzed documentation on how options are covered by the competitors. commercetools and Shopify attack this domain with product variants that represent all the possible options. Despite the domain simplification by reducing the number of entities both systems constrained with the size of a product which makes it impossible to compose complex business cases that we frequently may face in the Magento ecosystem.
Commercetools: Modeling Products
Bigcommerce has a more complex option representation then Shopify or Commercetools (which is not weird due to a large number of Magento ex-employes employed by Bigcommerce nowadays). in some way, I would compare their options features with Magento 2 x functionalities. Bigcommerce segregates options from variants and allows to associate intersection of options with a new product, and as a result, I believe, struggles from the same disease as Magento does - redundancy in the variant data.
The great advantage of Magento 2 - modularity was not a real thing for the product options during Magento the lifetime. Since times of Magento 1.x option prices coupled into options, inventory records ignored even options with assigned SKUs. The only thing that had to play it together "Bundle" product was designed too complex to treat it as universal solution it had to become.
That's why, with the storefront project, we have an opportunity to resolve it.
Taking into account the Magento experience (good, bad, ugly) and solutions proposed on the market. I could define the set of requirements that I would want to see in a new options implementation:
-
Options are a predefined list of possible product customizations. This list belongs to the product exclusively; it is measurable, and limited only by the business requirements. So it always easy to return even a complete set of options for rendering PDP initially.
-
Variants are a unique intersection of one or may option values. The variant matrix does not belong to a product as property. The variant matrix is stored managed separately from products. The primary role of variants is to distinguish the possible intersections of options from impossible. The variants matrix is never meant to be returned to the storefront as is. The variants matrix will be used for filtering options allowed at the storefront after one or many options selected.
-
Variants could link the options intersection with a product, a price, or an inventory record but this is not mandatory.
As a result, we can design options/variants schema minimalistic.
syntax = "proto3";
message Product {
repeated ProductOption options = 100;
}
message ProductOptionValue {
string id = 1;
string label = 2;
string sortOrder = 3;
string isDefault = 4;
}
message ProductOption {
string id = 1;
string label = 2;
string sortOrder = 3;
string isRequired = 4;
string renderType = 6;
repeated ProductOptionValue values = 5;
}
message ProductVariant {
repeated string optionValueId = 1;
string id = 2;
}
To explain data and operations, Lets review pseudo SQL schema, which describes the picture above. This is not the instruction for implementation and mostly purposed to show data and structures we have in the domain. Also, it should show the complexity of operations that we have to design on the final.
Tables products, product_options, product_option_values, product_variant represents visionary catalog domain entities.
create table products (
object_id char(36) not null,
name varchar(128) not null,
primary key (object_id)
);products stores registry of products.
create table product_options (
option_id char(36) not null,
object_id char(36) not null,
label varchar(64),
sort_order smallint,
is_required tinyint not null default 0,
render_type enum('checkbox', 'radiobutton', 'select', 'multiselect'),
primary key (option_id)
);
alter table product_options
add foreign key fk_product_options_object_id (object_id) references products(object_id);product_options - represents registry of characteristics that allows customization,
for instance, for apparel items, it could be "Color" and "Size".
create table product_option_values (
value_id char(36) not null,
option_id char(36) not null,
label varchar(64) not null,
sort_order smallint,
is_default tinyint,
primary key (value_id)
);
alter table product_option_values
add foreign key fk_product_options_product_id (option_id) references product_options(option_id);product_option_values - actual values that could be used to customize products, categorized by options.
create table product_variants (
value_id char(36) not null,
object_id char(36) not null,
primary key (value_id, object_id)
);
alter table product_variants
add foreign key fk_product_variants_value_id (value_id) references product_option_values(value_id);product_variants - Correlation between option values and variants, by using this correlation, we can say which of option combination is real.
The following script models data from the picture above. Starting here I will use fancy values for primary keys instead of UUID to make further scripts more readable. I assume that the human eye cannot efficiently analyze tens UUID signatures.
insert into products (object_id, name) values ('t-shirt', 'T-Shirt');
insert into product_options (option_id, object_id, label, sort_order, is_required, render_type)
values
('color', 't-shirt', 'Color', 0, 1, 'select'),
('size', 't-shirt', 'Size', 1, 1, 'select')
;
insert into product_option_values (value_id, option_id, label, sort_order, is_default)
values
('red', 'color', 'Red', 0, 0),
('green', 'color', 'Green', 0, 0),
('l', 'size', 'L', 0, 0),
('m', 'size', 'M', 0, 0)
;
insert into product_variants (value_id, object_id)
values
('l', 'l-red'), ('red', 'l-red'),
('m', 'm-red'), ('red', 'm-red'),
('m', 'm-green'), ('green', 'm-green');
So far, all looks pretty nice with such an approach product may return information for all available options with the single request.
mysql> select p.name, po.label as option_label, pov.label as option_value_label
-> from products p
-> inner join product_options po on p.object_id = po.object_id
-> inner join product_option_values pov on po.option_id = pov.option_id
-> where p.object_id = 't-shirt';
+---------+--------------+--------------------+
| name | option_label | option_value_label |
+---------+--------------+--------------------+
| T-Shirt | Color | Green |
| T-Shirt | Size | L |
| T-Shirt | Size | M |
| T-Shirt | Color | Red |
+---------+--------------+--------------------+
4 rows in set (0.00 sec)Let's assume that we have chosen one option value from the list. Starting this point we can look into variants to analyze remaining options. From the proposed example, we have chosen "Size": "M".
mysql> select pv.object_id
-> from product_variants pv
-> where pv.value_id in ('m');
+-----------+
| object_id |
+-----------+
| m-green |
| m-red |
+-----------+
2 rows in set (0.00 sec)
As you may see, our selection has matched two variants, but could we be sure that this variant matched completely to a shopper selection? And if No which options remain for the shopper?
mysql> select
-> pv.object_id,
-> pv.value_id,
-> count(pv.object_id) over (partition by pv.object_id) as values_in_variant
-> from product_variants pv
-> where pv.object_id in (
-> select pv.object_id
-> from product_variants pv
-> where pv.value_id in ('m')
-> );
+-----------+----------+-------------------+
| object_id | value_id | values_in_variant |
+-----------+----------+-------------------+
| m-green | green | 2 |
| m-green | m | 2 |
| m-red | m | 2 |
| m-red | red | 2 |
+-----------+----------+-------------------+
4 rows in set (0.00 sec)As you may see, each variant consist of two option values, so one more should be selected, and the only two options values remain un-selected except the size we already choose. By using this information we can limit the number of options and values to be shown to the shopper after selection.
mysql> select p.name, po.label as option_label, pov.label as option_value_label
-> from products p
-> inner join product_options po on p.object_id = po.object_id
-> inner join product_option_values pov on po.option_id = pov.option_id
-> where p.object_id = 't-shirt' and pov.value_id in ('red', 'green');
+---------+--------------+--------------------+
| name | option_label | option_value_label |
+---------+--------------+--------------------+
| T-Shirt | Color | Green |
| T-Shirt | Color | Red |
+---------+--------------+--------------------+
2 rows in set (0.00 sec)
Note: To achieve more advanced behavior, the variants could be "uneven" inside the single product. For instance, you would like to track only t-shirts XL: size separately for some reason (a different price or stock). The example above focused on covering the main case scenario. Still, the approach, overall, is meant to support extending the logic of resolving option values onto a variant under the hood.
