The following is a pretty conventional setup.
One row will be present in this table for every category:
CREATE TABLE Category
(
CategoryId int not null identity(1,1)
constraint PK_Category
primary key clustered
,Name varchar(50) not null
,(etc.)
)
Sample data:
1 Metal Handles
2 Wooden Handles
3 Plastic Handles
One row will be present in this table for every product:
CREATE TABLE Product
(
ProductId int not null identity(1,1)
constraint PK_Product
primary key clustered
,Name varchar(50) not null
,(etc.)
)
Sample Data:
101 Pots
102 Pans
103 18th Century Composers
And one row will be present in here for every relationship between a product and a category
CREATE TABLE ProductCategory -- Seems more suitable than "CategoryProduct"
(
ProductId int not null
constraint FK_ProductCategory__Product
foreign key references Product (ProductId)
,CategoryId int not null
constraint FK_ProductCategory__Category
foreign key references Category (CategoryId)
,constraint PK_ProductCategory
primary key clustered (ProductId, CategoryId)
)
Sample data:
101 1
101 2
101 3
102 1
102 2
103 2
Here, product "Pots" is associated with all three handles, while "Pans" is associated only with two. On the flip side, category "Metal" is associated with two products, while "Wood" is associated with three.