Dilemna!!!

[kamy99]

Hi all:

I have two tables; Products, Categories.

They're linked through the CatID field.
My problem is that any given product could belong to more than one category.
I need to know how to address this.

The problem is that the way things are now, I have to re-insert the product , each time referencing a different category. However on my list-all-products page, that product gets listed multiple times. I know I probably could use DISTINCT in my sql query, but is there any other way/method?

Thanx in advance
Kamy

[MrAlaska]

You have a many to many relationship. In a properly designed database, you need a parent table to link them up which will have a one to many relationship. In this case, you might make the category table the parent and each product would only be allowed one category. Then you could have a subcategory table which is a child of the category table also. Then you would have a single parent table to link them through. The actual design would depend on your needs, but the one -> many relationship is the important part.

You can probably work around it through your code, but normalizing the database will save you headaches in the long run.

[kamy99]

I still need more help/advice on this topic !!

[vallette]

Hey Kamy,

You may already have a solution to this but just in case here's an explaination of what Mr. A was describing.

You need 3 tables; products, catagories, and productcatagory. The products table holds info specfic to each product--name, price, id, et. and the catagories table holds info specific to the each of the catagories--name, id, etc. The thrid table only needs 2 fields--productid and catagoryid--and 'joins' the other tables together.

Every product has one and only one entry in the products table and every catagory has one entry in the catagories table but they can each have multiple records in the productcatagory table.

Lets say product id 1 is in catagories, 4, 7, 12. There'd be one record with product id 1 in the products table and one record for each of the three catagories in the catagories table but the productcatagory table would have three records for product id 1:

productid | catagoryid

1 4
1 7
1 12

To list the products that fall under catagory 4 you could do the following select:

select products.name
from products, productcatagory
where productcatagory.catagoryid=4 and productcatagory.productid=products.id

or to find out which catagory the product with id 1 is in:

select catagories.name
from catagories, productcatagory
where productcatagory.productid=1 and catagories.id=productcatagory.catagoryid

Hope this helps,
Dave