Jump to content

[Answered] Database Relationship Question


Recommended Posts

Hi folks.  For my CubeCart installation, I'm working on an external program that will update the stock level and price for my inventory.  This is for synchronizing with our eBay, Amazon, and other marketplaces to ensure consistent pricing (we auto change prices several times a day) as well as to ensure stock levels are correct.   Looking at the CubeCart database, it is a simple matter to update the CubeCart_inventory table for many products.  However, some products are product options with separate sku, upc, quantities and price.  The CubeCart_option_matrix table holds the stock level and product code for the options.  However, the pricing for these options is in the table CubeCart_option_assign.  There is no product_code (for the option) in this table.   I also cannot find an option level relationship in the CubeCart_option_matrix table to relate the table to.  Does anyone know how the relationship in the CubeCart_option_assign is defined to the option level product code?  I know it is related as I have observed how the data updates when you change something in the system so I know the relationship is there.  Thanks for any assistance.

Link to comment
Share on other sites

"some products are product options"

Such as? Would you say that a small-red shirt and large-blue shirt are different products, according to how you describe it above? So "product options" could be called 'option_matrix' database records.

"The pricing for these options is in the table CubeCart_option_assign. There is no product_code (for the option) in this table."


Then you start using the term 'option level'. By what aspect of products and options is this referring to? Stock levels?

You are wanting a relationship between CubeCart_option_matrix and what other data item?

Link to comment
Share on other sites

Thanks for your assistance.  I'll try to clarify.  Some products have product options.  These options each have their own sku (product_code), upc, stock level, and pricing.   The stock level for the options are in the table CubeCart_option_matrix.  CubeCart_option_matrix also holds the product_code.  So I can relate external data for the stock to the product_code in this table because the shared field is product_code.   However, the price for each option is in the CubeCart_option_assign table.  There is no product_code in the CubeCart_option_assign table so I have no way to know which price is for which product_code (option ).   I'm trying to find a way to update the price for the options.


Link to comment
Share on other sites

It will be very tricky. The CubeCart developers say it will be impossible.

"It" being finding the solution basically starting with the 'options_identifier' in the CC_option_matrix table, then discovering the option_id and value_id in the CC_options_assign table and adjusting the price there.

The essential problem is that each record in the CC_option_matrix comprises more than one actual option (regardless of your specific inventory), such as color and size. In this case, a single product variant will have two records in CC_option_assign:
option_id = 1 (Color), value_id = 1 (Red)
option_id = 2 (Size), value_id = 2 (Small)

If we can assume the names of the option groups (Color, Size) and the names of the option values (Red, Small) remain steady, we can:

1. Start with the product_code in CC_option_matrix, get options_identifier and cached_name.
2. Decompose the cached_name into an option_name (CC_option_group) and value_name (CC_option_value).
3. Collect all records from a JOIN of CC_option_group and CC_option_value ON option_id
4. For each record in that recordset, use the option_id/value_id pairs to calculate an options_identifier.
5. Whatever matches the target options_identifier, we have found the appropriate set of assign_id records in CC_option_assign.

However, CubeCart applies the price differential from each option choice. So, working backwards, the decision on how to apportion the total price differential remaining after subtracting the product's base price still needs to be solved.

Link to comment
Share on other sites

Thank you.  I thought about doing it as you suggest.  However, there has to be a more normalized way to define the relationship.  The platform is somehow defining the relationship as it updates the associated information.  I seriously doubt it is splitting these text values to define the relationship.  That would be a major performance hit in the database. 

Link to comment
Share on other sites

"splitting these text values"

In the forward progression of finding options assigned to a product, then completing the model for the view, there isn't "splitting text values".

But it is what we have when working backwards.

Link to comment
Share on other sites

After spending some time reviewing the functions / classes in the Cube Cart source, I managed to figure it out. It took a long time though.  The code is fairly clean but I'm not a PHP expert.  The lack of good comments in the code is a hindrance (and disturbing for professional grade software ).  Maintenance would be a nightmare for someone just coming on.  

In any case, for some reason, the option id and value id are getting hashed and are a field (option_identifier) in the CubeCart_option_matrix.  To update the price in the CubeCart_option_assign table, I will need to determine the hash for each option_id and value_id in that table.  I can then relate the CubeCart_option_matrix table, to the CubeCart_option_assign table using product_id and the option_identifier (hashed).  The best approach (for me) would be to modify the CubeCart_option_assign table by adding an Option_Identifier column and use md5() in PHP to create the associated id.  I will backup and test this tomorrow.

This is just crazy though.  There is no reason to apply a hash to those values.  They are not something that needs to be encrypted (weakly encrypted at that).  There are also a lot of things done in the code that would have been better off being stored procedures (or the equivalent in MySQL) rather than building and executing SQL statements from the PHP pages but I guess that is a design choice.  Overall I'm very pleased with the software.  The software is luxurious compared to x-cart.


Link to comment
Share on other sites

"I will need to determine the hash for each option_id and value_id in that table."

I think that's what my description posted earlier laid out.

"There is no reason to apply a hash to those values."

The hash is mainly used to group option_id/value_id pairs of more than one option that constitutes an option combination: Color:Red,Size:Small. (I see an integral flaw in this approach.)

"stored procedures"

I'm sure that's coming in CC7.


Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...