[ic] Single SKU in Multiple Categories

[interchange-users@interchange.redhat.com (Ian Riddler)]

Hi Patrick,
This is exactly what I am installing. (interchange 4.8.3)

Once you have the 3 tables, how do you do the last step - making html links
that return the list of products based on the category?

This query returns the correct results in MySQL :

select distinct products.sku as sku from products, bookcat, bookcat_book
where
bookcat.category = "Children" and products.sku = bookcat_book.sku and
bookcat_book.cat_id = bookcat.cat_id;

(Products is the standard products table, bookcat is the list of categories
and cat_id's, and bookcat_book is the link table, containing only sku and
cat_id)

The end result I'd like is that the category_vertical component has links
that work using this structure.
I'm presently looking at the bar_link code in catalog_before.cfg, and
thinking, "Gee, I hope someone has done this before!"

Can you help ?

Kind Regards,
Ian Riddler

----- Original Message -----
From: <patrick.bennett@ccgenesis.com>
To: <interchange-users@interchange.redhat.com>
Sent: Saturday, December 22, 2001 9:38 AM
Subject: RE: [ic] Single SKU in Multiple Categories


>
> > -----Original Message-----
> > From: Kevin Walsh [mailto:kevin@cursor.uk.com]
> > Sent: Friday, December 21, 2001 12:38 PM
> > To: interchange-users@interchange.redhat.com
> > Subject: Re: [ic] Single SKU in Multiple Categories
> >
> >
> > > I am looking at alternatives of having a single SKU show up
> > in multiple
> > > categories. Looking through the archives, the best way to
> > do this easily
>
> [snip]
>
> > >
> > By far the best way would be to have one table for your categories,
> > one table for your products and a third "combination association"
> > table to link the two.  The third table would only need two columns:
> > "category" and "sku".  A non-unique index on each column will be
> > useful.
> >
> > Coding multiple values into a single category column in the products
> > table will prove more trouble than its worth.  Any passing DBA will
> > not be able to stop himself from yelling out strange terms like
> > "Normalisation" and "1NF" whilst pointing at you in an "Invasion of
> > the Body Snatchers" sort of way. :-)
> >
> > The complication will come in the administration:  You will need a
> > method of assigning SKUs to categories using this new table and,
> > unless you use a database with cascade-delete facilities, you will
> > need a method of removing the association records when either an SKU,
> > or a category is deleted.
> >
> > I've done this before, and its no bother.  Just plan what you want,
> > and how you want to administer it, before you start.
> >
> > Best of luck.
>
> I've done this with the site I'm working on and it works great.  The
> tables are called section and section_product.  The section has a name,
> a description and an image, and section_product has section_id and sku.
> I then created a PHP interface for the client to drop products into
> sections by sku (and to edit the description and attach the image).
>
> I also have a section_display table which allows me to link sections
> in any arbitrary hierarchy.  The client wanted 3 levels of hierarchy,
> so it seemed the best way to go.
>
> Patrick
> _______________________________________________
> interchange-users mailing list
> interchange-users@interchange.redhat.com
> http://interchange.redhat.com/mailman/listinfo/interchange-users
>
>