Preventing Redundant Data in the Database

[Tryst]

Hi all,

I have a problem where I may have to contain duplicates of products in a products table where some products belong to two different categories (from a categories table). But I don't want duplicates, and instead would prefer a work around where I can relate a particluar product to two categories.

In the products table I have a field named 'parent_id' where this is a foriegn key for the primary key of the categories table. But, now my client has issued me with a new set of categoires, some of which contain products that are already in the database and that belong to another category.

Is there any way I can link products to two categories, with out adding an additional column to the products table?

For example:

If I have the following category (in a categories table)...
cat_id | name
20 - Winsor Art Colours
21 - Technical Brushes

and the following Product (in a products table)...
item_id | parent_id | name
150 - 20 - Red sky brushes

How can I make make the product (item_no: 150) also relate to category 21?

Thanks

Tryst

[MattR]

Remove parent_id column from product table.
Add a new table which contains only product id and parent ID. Make both columns the primary key. Voila.

[Tryst]

Cheers MattR, but is there another way around this as I have embedded quite a few queries within my PHP to handle the tables as there are.

Cheers

Tryst

[r937]

yes there is another way, but it is way more painful than what mattr suggested

bite the bullet, admit that the "product can belong to only one category" design rule was shortsighted, add the relationship table, and update your queries