same product in multiple categories and subcategories?

[bpdsol]

Hi all,

 

If anyone has an opinion on how to approach the following, I would appreciate hearing it.

 

We want to sell an auto part - just one type of part/product. However there are about 1000 different part numbers for various makes, models and years, and there are three versions of each part number with different random prices for each version.

 

Not counting the 3 different versions of each part, our table of vehicle applications (years-make-model) has about 5000 records. If each version was made a unque part number the table would triple in size to about 15,000 records.

 

Obviously, the same part number is usually used on many different cars.

 

In searching for paid professional help to do this typically they assume that they will make categories and subcategories corresponding to make, model, and year.

 

Isn't that approach problematic considering that the same product can go in a hundred different make-model-year i.e. category-subcategory-subcategory, combinations? Isn't all that redundancy a bad idea?

 

The way I see it, in the osCommerce terminology, we only have one category.

By the way, as it turns out, we also only have one manufacturer.

 

I was thinking about adding the products to the osc_products table, then creating a new table with years, makes models, and, corresponding products_id. Therefore, there would be many records in the new table with the same products_id. Then we would add a search function of the new table based on make model and year combinations, and the corresponding products_id results would ne used to pull products from the osc_products table.

 

Maybe I'm just micromanaging and should just pay up and give them free reign...

 

Thanks to anyone patient enough to read this !

[GraphicsGuy]

Personally, I think the category/subcategory method is going to be the most intuitive for your shoppers. The redundancy isn't really what it seems. I'm not quite sure how it would be done importing such a large number of products into your store (i.e. through easy populate), but I do know that osc has built in functionality for copying products. When a product is copied you have the option of duplicating or just linking it. When it is linked, it is not redundant and any change made to the product's details in any one category/subcategory is also reflected in all other categories that product is displayed in.

 

So you would have a category/subcategory system to help your customers find the product for the year/model of their vehicle. And one single product would appear in as many different cat/sub's as you designate it to. Ergo, ease of shopping and no redundancy.

[Guest]

Personally, I think the category/subcategory method is going to be the most intuitive for your shoppers. The redundancy isn't really what it seems. I'm not quite sure how it would be done importing such a large number of products into your store (i.e. through easy populate), but I do know that osc has built in functionality for copying products. When a product is copied you have the option of duplicating or just linking it. When it is linked, it is not redundant and any change made to the product's details in any one category/subcategory is also reflected in all other categories that product is displayed in.

 

So you would have a category/subcategory system to help your customers find the product for the year/model of their vehicle. And one single product would appear in as many different cat/sub's as you designate it to. Ergo, ease of shopping and no redundancy.

Before deciding the most efficient process you need to quantify the no. of unique manufacturers products and their price variations. If this database is quite small and used as the products table then you could get efficiencies using your proposal, i.e. a pre-lookup table, the order output to the customer would show the manufacturers id.

[Thieving_Gypsy]

Before deciding the most efficient process you need to quantify the no. of unique manufacturers products and their price variations. If this database is quite small and used as the products table then you could get efficiencies using your proposal, i.e. a pre-lookup table, the order output to the customer would show the manufacturers id.

 

 

I have a similar problem to that described - my shop has 550 products but 7500 categories and the linking between products and categories means there are 37000+ entries in the products_to_categories table. This is casusing teh site to be very slow and I'm looking for a way around that. Any ideas anybody?

[bpdsol]

Sincere thanks for the replies - and I apologize for the delay in posting again.

 

There's no substitute for independent experimentation and observation, so I decided to try it on a live server.

 

The result of page loading and browsing experiments has been positive, especially after turning off category counting. I have been able to populate the store easily using phpMyAdmin, so I don't see any reason to experiment with a contrib for that purpose.

 

As it stands I have about 1000 products and about 5000 rows in the products_to_categories table. Even in IE 5.5 and 6 the page loads in under 10 seconds on a dial-up connection at 44kbps. Performance is better in Opera and Firefox (for the record, there are no graphics yet). The top level category is vehicle make, below that is subcat Model, and below that is a subcat for range of years. The products only reside in the lowest level subcat.

 

This is not the full product population but I'm satisfied that this is an acceptable data architecture. Now I need to hire someone to code some dropdown boxes for cat-subcat-subcat, e.g. pick the top level cat from the dropdown and the subcat in the next dropdown populates, then pick one of those and the last subcat populates, then pick a final subcat and click 'search'. It should work nicely. There's a contrib almost exactly for this, but I haven't been able to get it to work properly...

 

http://www.oscommerce.com/community/contri...ons,2306/page,4

 

oh well...