• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

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
0
rgb192
Asked:
rgb192
  • 2
  • 2
  • 2
2 Solutions
 
Pratima PharandeCommented:
you idea is correct
0
 
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
 
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now