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