[ic] Single SKU in Multiple Categories

[interchange-users@interchange.redhat.com]

> -----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