Projects:Reference for Product Variants and Characteristics/Technical Documentation

Overview

The purpose of this project is to define a new UI Reference to visualize Product Characteristics.

Structure

• Characteristics are persisted in a sub table of m_product (m_product_ch_value)
• There is a new non-editable column in m_product where characteristic values are saved in a serialized manner
• Characteristic values have a tree structure, for example: Green can be the parent of “Green tone 1” and “Green tone 2”

Form view

For each product, characteristics are shown in a new section as new non-editable fields.

Grid view

• The filtering is done on the new column.
• Filter cannot be directly edited.
• To edit it, a button is clicked that opens a popup where all possible characteristic types are displayed.
• For each type multiple values can be selected.
• The final query is based not in the new column but in the actual product’s subtable.
• The way it works is: (C1V11 or C1V12 or C1V12) and (C2V21 or C2V22) and (C3V31)

different characteristics are linked with an “and” between them different values for each characteristic are linked with an “or” between them

Other considerations

• Product selector might implement this way of filtering for characteristics
• It might be desirable to use it (through computed fields?) in other tabs such as order line
• Product characteristics are not directly editable from product window. In case it is needed a new process might be implemented to support it.

Performance evaluation

In order to test the performance of the product characteristics reference the following scenario was created: - 6 different characteristics, 4 of them with 10 values, 1 with 3 and 1 with 2. - High volume of products, from 10.000 to 30.000

The initialization of the product characteristics tree is very fast and it scales very well.

The query done in the m_product table when this filter is used has good performance, it does not use sequential scans. This image shows how this query is structured when products are filtered using two different characteristics:

Executing this query took 151-168 miliseconds for 10.000 products, 153-173 for 20.000 and 156-177 for 30.000. Executing it with ten thousand more new products only takes a few miliseconds more.