In the Pioneering Cycling site, we wanted to subclass both the product and SKU item types. Subtypes automatically inherit all of the attributes from a parent item type, but can have new properties that apply only to that one subtype. Creating subtypes is useful when one subtype needs a property another subtype doesn’t. If all possible attributes were added to the base item-type, users might enter values for certain properties that were not relevant for all items.
For example, we created three new SKU item types: clothing-sku, bike-sku and part-and-accessory-sku. All three of these new SKU items types are subtypes of SKU. This means that the new item types inherit all of the attributes that a SKU has, but each can have some new attributes. When you create a new SKU in the ACC, you can specify that it be of item type bike-sku for example. The ACC then allows you to specify all of the properties that are associated with a bike-sku.
Product subtypes
We created two new subtypes for products: frame-product and frame-fit-product.
When we created new products with frames (such as a bike), we made the product of type frame-product. This frame-product subtype has a new property called frame-type that is only relevant for frame-products.
If the product was a part that fits on a particular bike frame (such as a crankset), we made it of type frame-fit-product. This subtype has a new multi-valued property called compatibleframes that contains a list of all of the frame types this part is compatible with. All other products, such as helmets, are simply of type product.
We wanted to capture this information on our products to provide customers with the following types of functionality:
Show all the parts with a particular bike.
Show all the bikes compatible with a particular part (for example, a brake).
Warn customers when they add products that are incompatible to their shopping carts.
This is how we updated the XML script for the product item-descriptor to specify the two new subtypes of product (frame-product and frame-fit-product):
<item-descriptor name="product"> … <table name="dcs_product" type="primary" id-column-name="product_id"> <property name="type"> <attribute name="useCodeForValue" value="false"/> <option value="product" code="0"/> <option value="frame-product" code="1"/> <option value="frame-fit-product" code="2"/> </property> </table> … </item-descriptor>
Because frame-product subtypes have a new attribute called frame-type, we created a table to hold this information with this SQL DDL:
CREATE TABLE b2c_frame_product ( product_id VARCHAR(40) NOT NULL REFERENCES dcs_product(product_id), frame_type INTEGER NULL, PRIMARY KEY(product_id) );
We also had to add a new item-descriptor for the new frame-product subtype. Here is the XML definition for it. Notice that by specifying super-type="product" this item type becomes a subclass of the existing product item type, and then inherits all properties from it. The frame-type property is enumerated. When you create a product of type frame-product, ACC provides you with a dropdown list of possible values for the frameType value (A, B, C, etc.). Also, the frame-type property value is stored in the b2c_frame_product table created above.
<item-descriptor name="frame-product" super-type="product" sub-type-value="frame-product" id-space-name="product"> <table name="b2c_frame_product" type="auxiliary" id-column-name="product_id"> <property name="frameType" data-type="enumerated" column-name="frame_type"> <option value="A" code="1"/> <option value="B" code="2"/> <option value="C" code="3"/> <option value="D" code="4"/> <option value="E" code="5"/> <option value="F" code="6"/> <option value="G" code="7"/> <option value="H" code="8"/> <option value="I" code="9"/> </property </table> </item-descriptor>
Because frame-fit-product subtypes have a new multi-valued attribute called compatibleframes, we created a table to hold this information. Here is the SQL DDL to do that. Notice that the column name (frame) does not necessarily need to match the property name (compatibleframes).
CREATE TABLE b2c_compat_frame ( product_id VARCHAR(40) NOT NULL REFERENCES dcs_product(product_id), sequence_num INTEGER NOT NULL, frame INTEGER NOT NULL, PRIMARY KEY(product_id, sequence_num) );
We also added a new item-descriptor for the new frame-fit-product subtype. Here is the XML definition for it. Again, note that this subtype inherits from the product super type. It has one attribute (compatibleframes), a multi-valued attribute whose data is stored in the b2c_compat_frame table.
<item-descriptor name="frame-fit-product" super-type="product" sub-type-value="frame-fit-product" id-space-name="product"> <table name="b2c_compat_frame" type="multi" id-column-name="product_id" multi-column-name="sequence_num"> <property name="compatibleframes" data-type="list" component-data-type="string" column-name="frame"/> </table> </item-descriptor>
SKU subtypes
For the Pioneering Cycling site, we created three new SKU item types: clothing-sku, bike-sku and part-and-accessory-sku. All three of these new SKU items types are subtypes of SKU.
Here is the XML for adding the three new subtypes. It was added to the SKU item-descriptor:
<item-descriptor name="sku" sub-type-property="type"> <table name="dcs_sku" type="primary" id-column-name="sku_id"> <property name="type"> <option value="sku" code="0"/> <option value="clothing-sku" code="1"/> <option value="bike-sku" code="2"/> <option value="part-and-accessory-sku" code="3"/> </property> </table> … </item-descriptor>
Clothing-SKU
The clothing-sku item type has the following three new enumerated properties:
Property | Description |
|---|---|
| Indicates the size of a garment (S,M, L, XL) |
| Used for storing what a garment is impervious to (for example, waterproof or windproof). |
| Indicates the product material (for example, cotton, spandex). |
These new attributes are stored in a new table in the database called b2c_clothing_sku. Here is the SQL DDL for creating this new table.
CREATE TABLE b2c_clothing_sku ( sku_id VARCHAR(40) NOT NULL REFERENCES dcs_sku(sku_id), clothing_size INTEGER NULL, proof INTEGER NULL, material INTEGER NULL, PRIMARY KEY(sku_id) );
Here is the XML definition for the clothing-sku item-descriptor. Notice that by specifying super-type="sku" this item type is a subclass of the exiting SKU item type, and inherits all properties from it. The properties are all enumerated so the ACC provides dropdown lists when entering SKU values:
<item-descriptor name="clothing-sku" super-type="sku" sub-type-value="clothing-sku" id-space-name="sku" display-name="Clothing SKU"> <table name="b2c_clothing_sku" type="auxiliary" id-column-name="sku_id"> <property category="Pioneer Cycling - Clothing" name="size" data-type="enumerated" column-name="clothing_size" display-name="Size"> <option value="S" code="0"/> <option value="M" code="1"/> <option value="L" code="2"/> <option value="XL" code="3"/> </property> <property category="Pioneer Cycling - Clothing" name="proof" data-type="enumerated" column-name="proof" display-name="Proof"> <attribute name="useCodeForValue" value="false"/> <option value="None" code="0"/> <option value="Water" code="1"/> <option value="Wind" code="2"/> <option value="Child" code="3"/> <option value="Crash" code="4"/> <option value="Tear" code="5"/> <option value="Dent" code="6"/> <option value="Fire" code="7"/> <option value="Monkey" code="8"/> </property> <property category="Pioneer Cycling - Clothing" name="material" data-type="enumerated" column-name="material" display-name="Material"> <attribute name="useCodeForValue" value="false"/> <option value="Unknown" code="0"/> <option value="Cotton" code="1"/> <option value="Spandex" code="2"/> <option value="Kevlar" code="3"/> <option value="Nylon" code="4"/> <option value="Polyester Blend" code="5"/> <option value="Silk" code="6"/> <option value="Wool" code="7"/> <option value="Goretex" code="8"/> </property> </table> </item-descriptor>
Bike-SKU and Part-and-Accessory-SKU
The new bike-sku and the part-and-accessory-sku item types have the following attributes:
Property | Description |
|---|---|
| The type of metal used to make the bike. |
| A map where the keys are the names of the dimensions measured and the values are the measurements. |
The bike-sku and part-and-accessory-sku item types have exactly the same properties; they could be combined into a single item type. The reason for keeping them separate is that if we want to know if a particular SKU is a bike or a part, we can test the item type and have the answer easily.
Here is the SQL DDL script for the new b2c_bike_sku table created to store the new metal attribute:
CREATE TABLE b2c_bike_sku ( sku_id VARCHAR(40) NOT NULL REFERENCES dcs_sku(sku_id), metal VARCHAR(40) NULL REFERENCES PRIMARY KEY(sku_id) );
Because the dimensions attribute is multi-valued, the values are stored in a separate table (b2c_dimensions). The dimensions attribute is accessed as a Map. The Map type provides a flexible way of storing multiple key/value pairs. The list of possible keys is not hard coded or predetermined. When fetching a value from a Map attribute, we pass in a key and the Map returns the associated value. So, if the dimensions Map value for a particular bike was {tubeLength=5.0,pedalDiameter=6.0} and we passed in the key: tubeLength, it would return the value 5.0.
Here is the SQL DDL creating the table to store the dimensions property. Note that the tag column holds the key, and the measurement column holds the value.
CREATE TABLE b2c_dimensions ( sku_id VARCHAR(40) NOT NULL REFERENCES dcs_sku(sku_id), tag VARCHAR(100) NOT NULL, measurement DOUBLE PRECISION NULL, PRIMARY KEY(sku_id, tag) );
Here is the XML file for creating the bike-sku item type:
<item-descriptor name="bike-sku" super-type="sku" sub-type-value="bike-sku" id-space-name="sku"> <table name="b2c_bike_sku" type="auxiliary" id-column-name="sku_id"> <property name="metal" data-type="enumerated" column-name="metal"> <attribute name="useCodeForValue" value="false"/> <option value="Unknown" code="0"/> <option value="Aluminium" code="1"/> <option value="Chromoly" code="2"/> <option value="Steel" code="3"/> <option value="Carbon" code="4"/> <option value="Titanium" code="5"/> </property> </table> <table name="b2c_dimensions" type="multi" id-column-name="sku_id" multi-column-name="tag"> <property name="dimensions" data-type="map" component-data-type="double" column-name="measurement"/> </table> </item-descriptor>
After all of these changes were made, a particular SKU’s tubeLength value (stored in the dimensions Map property) could be displayed in the following simple (yet very dynamic) way. Remember that the Map keys are not hard-coded or predetermined, so any key stored in the Map can be displayed in this way:
<dsp:valueof param="Sku.dimensions.tubelength"/>
In summary, we have shown you how we customized the Pioneer Cycling product catalog to meet our needs by adding SQL DDL scripts, and XML item-descriptor files. Just about any customization your site needs is possible with the flexible ATG architecture.
