This is a technical description of the way Ubercart 1 & 2 Attributes/Options are implemented and is intended as a reference for the PHP developer working with Ubercart attributes. Lord have mercy on your soul.
Übercart (UC hence forth) started with what would seem the obvious assumption that:
(A) 1 Drupal Node (NID) = 1 Product (SKU)
which seems reasonable and instinctive, at first. Then Product Attributes where added as an afterthought and more importantly, a separate module with separate tables, forms etc. Now you have more than one Product to a Node. A One-to-Many relationship. Unfortunately the data model doesn't work this way. You now have 2 SKU's per Physical Product, one on the Node and one for the combination of attributes.
To add more complexity here, Product Classes were introduced so that one might define a template for a 'class' of products that share a similar Attribute collection i.e. a T-Shirt class might have 'size' + 'colour'. These classes (or prototypes) are store in the DB scheme and copied to the "instance" tables ('uc_product_attributes' & 'uc_product_options') when a new product of that class is created (by uc_attribute_nodeapi() function).
You'll notice that on the Class editing forms you can't directly create attributes or options, you have to:
- Create a regular Attribute via /admin/store/attributes/add (updates 'uc_attribute' table)
- Create an Option via /admin/store/attributes/%AID/options/add (which updates 'uc_attribute_options')
- Create a Class (insert into 'uc_product_classes' CLASS_NAME used bellow)
- Add the Attribute from 1 to your new class via /admin/store/products/classes/CLASS_NAME/attributes/add (insert ... select into both 'uc_class_attributes')
- You still then have to go to /admin/store/products/classes/%CLASS_NAME/options check the options you want for this class an then 'Submit' (which finally updates 'uc_class_attributes_options').
- Finally when you get to actually creating a Product of type CLASS_NAME then a copy of the class data is made for the Product instance (i.e a INSERT ... SELECT is done to copy 'uc_class_attributes' & 'uc_class_attributes_options' to 'uc_product_attributes' & 'uc_product_options' respectively)
- Create a product Node /node/add/%CLASS_NAME
- THEN to add SKUs to each combination of attributes for that product instance you need to submit the Product Adjustment form over at /node/%NID/edit/adjustments which updates the ugly 'uc_product_adjustments' table. The ugly part here is that the Attribute Option combination is a PHP object that gets serialised and stored in the DB. See Figure 2 for how it should have been modelled (well, consistently within this 1xNID=1xSKU paradigm)
There are some problems with this schema:
- Form a User Interface (UI) perspective long and tedious. There is no interface for creating attribute options on mass.
- Changes to the Product Class attribute/options doesn't reflext in existing instances of that class.
- When new Attribute Options are added then the uc_product_adjustments table has to be flushed, losing all user input and recreated again (well it doesn't have to be but thats the laziest way to code it else you'd have to pull all the records out so you could unserialise the options just to see whats already there, one of the many disadvantages to not Normalising your DB schema.
- Mostly this not consistent with (A) in that the realisation that the SKU is the real Product identifier in that each SKU represents a model in the stote room, the physical product. The Product with attributes is an illustion presented to the customer to make navigating the long list of every product combination easier to understand. Its conceptual not actual. In order to keep with (A) one would have to then create a Node for each Attribute Option combination. This coul be an option but then you'd have the replication of standard Node data like Title & Description for each, which would be a maintenance problem trying to keep them all in sync (and then translated!).
To top this off there is no API that one can use to create, update or delete Attributes, their Options and the associated 'Adjustments' (a ambiguous name for an ambiguous table with ambiguity being the opposite of what you want in a DB).
If you are forced to deal with this data your best off hitting the DB directly yourself.