assign a category to products without modifying products table

I do not want to modify products table because this is a live database

assign categories to products
example category
keyboards
monitors
headphones

I have one existing table (which has many rows)
int:
products.productid





my idea (may be wrong)

CREATE TABLE [dbo].[categoryfolders](
      [folderid] [int] IDENTITY(1,1) NOT NULL,
      [name] [varchar](100) NOT NULL,
      [active] [tinyint] NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[categoryfoldersproductid](
      [folderid] [int] ,
      [productid] [int]
)


categoryfoldersproductid only has one row per productid

so I have a query of insert or update or delete
LVL 1
rgb192Asked:
Who is Participating?
 
Pratima PharandeCommented:
Only one sugesstion if you want only one category per product then make  folder id and Product+id as promary key in [categoryfoldersproductid].did you need any other help in it ?
0
 
Pratima PharandeCommented:
you idea is correct
0
 
Scott PletcherSenior DBACommented:
I don't like the table name:

>> categoryfolders <<

Why "folder"?  Maybe the "folder" has a valid reason I'm not aware of.


If these categories apply only to products, then I would name the table:
product_categories

If the categories are more general, then just "categories".
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
rgb192Author Commented:
so
product_categories instead of categoryfolders

and

categories instead of categoryfoldersproductid


how should I create the columns
0
 
Scott PletcherSenior DBACommented:
CREATE TABLE [dbo].[categories] (
      [categoryId] [int] IDENTITY(1,1) NOT NULL,
      [name] [varchar](100) NOT NULL,
      [active] [tinyint] NOT NULL      
) ON [PRIMARY]
CREATE UNIQUE CLUSTERED INDEX categories__CL ON dbo.categories ( categoryId ) ON [PRIMARY]

CREATE TABLE [dbo].[product_categories] (
      [productId] [int],
      [categoryId] [int]
)
-- drop UNIQUE if it doesn't apply / add another key if necessary
CREATE UNIQUE CLUSTERED INDEX product_categories__CL ON dbo.product_categories ( productId, categoryId ) ON [PRIMARY]
0
 
rgb192Author Commented:
thanks for the organization
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.