[ic] Adding same product to multiple categories

Kevin Walsh interchange-users@interchange.redhat.com
Tue Apr 30 20:04:01 2002


> 
> I am faced with the same dilemma. I am a newbie to Interchange (Running 4.8)
> and would appreciate any help on this subject. Here is my plan for the
> database, but as I said I am a newbie here and am not sure yet how I will
> implement this or if it is even possible. But here it goes:
> 
> Items Table
> 
> SKU		Name		Price
> 1234		Vit C		$25.00
> 1235		Vit D		$20.00
> 1236		Vit E		$15.00
> 1237		Vit F		$22.00
> 
> ProductID Table
> 
> ProdNum	Name
> 1		Supplements
> 2		Tack
> 3		Feed
> 
> CategoryID Table
> 
> CatNum		Name			ProductNum  	TableName
> 1		AntiOxidants		1			Table0001
> 2		Vitamins			1                 Table0002
> 3		Joint Supplements		1                 Table0003
> 4 		Hoof Supplements		1                 Table0004
> 
> Table0001
> 
> SKU
> 1236
> 
> Table0002
> 
> SKU
> 1234
> 1235
> 1236
> 1237
> 
> Table0003
> 
> SKU
> 1236
> 1237
> 
> Table0004
> SKU
> 1235 
> 
I don't like the idea of the multiple "table0000" tables.

To handle products that can appear in more than one category,
I'd suggest that you set up a "combination association" table
with two columns that you would use like this:

SKU     category
------- --------
1234    2
1235    2
1235    4
1236    1
1236    2
1236    3
1237    2
1237    3

Then you could select all SKUs that belong to category 2, for
instance, with a lot less trouble than you would have maintaining
several "identical" tables.

You would, of course, have no need for the "tablename" column in
your "categoryid" table if you used this sort of table.

-- 
   _/   _/  _/_/_/_/  _/    _/  _/_/_/  _/    _/
  _/_/_/   _/_/      _/    _/    _/    _/_/  _/   K e v i n   W a l s h
 _/ _/    _/          _/ _/     _/    _/  _/_/    kevin@cursor.biz
_/   _/  _/_/_/_/      _/    _/_/_/  _/    _/