Question on database structure

[Guest Agouti]

I've been heavily modding a cubecart installation and noticed a funny thing (2 infact):

The inventory field has both a image and cat_id field, however this isn't where cubecart actually stores the category: it stores it, in addition, in a cat_idx table, which has the productId and catId, meaning that all the data is 100% duplicated.

The only realistic reason for this is so that products can belong to more than one category, however, there is no functionality for this in the admin - and in any case, why then have a cat_is field in the inventory table?

The same applies to the image field. While cubecart reads off of this field, the data is duplicated in the image_idx table in the same way, with no support for actually utilising this in the admin.

My assumption is that these were added as a framework for future versions for products to be in multiple categories and have multiple images, however it is a bit of a hack to then keep the fields in the inventory table as well.

Might not make much of a difference if you only have a thousand products, but when you are doing external imports of 5000 odd products, it means twice as many imports and data processing.

Another field I don't understand the reasoning behind is the "noProducts" field. Is it simply for cubecart to grab for aesthetic purposes ({category} (#noProducts)) or is it actually used in a constructive way? If so, does the noProducts for a field have to include the noProducts for the daughter categories?

I am only asking for the same reason as above, that it takes a conciderable amount of server time to calculate - I can't even use Sir Williams script, it times out even after an hours churning. A OO script I've made seems to be able to get through it in a reasonable amount of time, but again, it all adds up for each import.

Sorry if I seem a little gruff to people who have donated their time to what is absolutly the best shopping cart to work with - it just feels so out of place in what in the rest of the cart is one of the most well laid out and most intuitive to work with and modify that I have ever found.

[Guest eric47905]

The only realistic reason for this is so that products can belong to more than one category, however, there is no functionality for this in the admin

There is functionality to place items in more than one category under admin. You would go to view products, then on the product you want click the manage category link, and you can add the item to other categories in addition to your main category.

Sorry though, I don't know answers to any of your other questions / muses.

[Guest]

Agouti, I've personally done an import of over 43,000 products with no problems whatsoever. So I'm not sure what's going on that's giving you such headaches.

The cats_idx table is indeed to support multiple categories for a given product. The cat_id in the _inventory table is the "base" category so to speak, but with the cats_idx table, you can have it in dozens of others WITHOUT having to duplicate the inventory data.

And the noProducts field is so that you don't have to calculate the number when you do category displays, so you were right about that.

:)

[Guest Agouti]

The cats_idx table is indeed to support multiple categories for a given product. The cat_id in the _inventory table is the "base" category so to speak, but with the cats_idx table, you can have it in dozens of others WITHOUT having to duplicate the inventory data.

The problem is that you cannot just set the cat_id in the inventory table - it just ignores it. You have to fill the cat_idx table regardless, so why have it at all?

That was what I was referring to in terms of duplication.

[Guest]

If you look at any given product in your store, it will tell you what category that product is in. That value comes from cat_id in the _inventory table. But by also having entries in the cats_idx table, you can let that product show up in several different categories without duplicating any data.....you just have multiple pointers.

:pirate: