assign a category to products table

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?
 
exodusterCommented:
You wrote this on the begining:
CREATE TABLE [dbo].[categoryfolders](
      [folderid] [int] IDENTITY(1,1) NOT NULL,
      [name] [varchar](100) NOT NULL,
      [active] [tinyint] NOT NULL
) ON [PRIMARY]

just add PRIMARY KEY:
ALTER TABLE [dbo].[categoryfolders]
ADD CONSTRAINT PK_categoryfolders_folderid PRIMARY KEY CLUSTERED (folderid)

And you need foregin key in table with products, but first you need to add that column:
ALTER TABLE dbo.products
ADD folderid int
and then:
ALTER TABLE dbo.products
ADD CONSTRAINT FK__products_categoryfolders__folderid FOREIGN KEY (folderid) REFERENCES dbo.categoryfolders (folderid)
0
 
exodusterCommented:
If ,,categoryfoldersproductid only has one row per productid'' means that one product can't be assigned to more than one category, it's ok. Of course good idea is also adding keys (primary and foreign).
0
 
rgb192Author Commented:
how to make sure
categoryfoldersproductid has only one row per productid

and which table to add primary/foreign keys
how
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.

 
exodusterCommented:
To make sure ,,categoryfoldersproductid has only one row per productid'' you can just simply add primary key on productId column.
To add primary key you can just simply add PRIMARY KEY CLUSTERED after definition of column:
[folderid] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED

To add foreing key:
ALTER TABLE [dbo].[categoryfoldersproductid]
ADD CONSTRAINT FK_categoryfoldersproductid_folderid FOREIGN KEY (folderid) REFERENCES [dbo].[categoryfolders] (folderid)
GO
0
 
exodusterCommented:
Of course primary keys are needed on both tables.
About one category to one product: it's about relation between those two ,,tables''. Is it one (category) to many (products) or many (categories) to many (products)? It depends on business  assumptions.
The above soultion will work to ,,one to many'' relation. If you need ,,many to many'' relation then you need another table.
0
 
rgb192Author Commented:
one (category) to many (products)
0
 
exodusterCommented:
Sorry, so what i wrote is good for many to many relation (i didn't catch: products.productid :>).
So you dont need ,,categoryfoldersproductid'' table. You just need table with categories and foreign key in your table with products. Of cours you need column like ,,folderid'' in table with products and foreign key on that column.
0
 
rgb192Author Commented:
>>
Sorry, so what i wrote is good for many to many relation (i didn't catch: products.productid :>).
So you dont need ,,categoryfoldersproductid'' table. You just need table with categories and foreign key in your table with products. Of cours you need column like ,,folderid'' in table with products and foreign key on that column.


you wrote what I DO NOT need

so what do I need
0
 
exodusterCommented:
As i wrote:
,,You just need table with categories... and you need column like ,,folderid'' in table with products and foreign key on that column.''

Good luck!
0
 
rgb192Author Commented:
>>
,,You just need table with categories... and you need column like ,,folderid'' in table with products and foreign key on that column.''

could you show me a create table example of this
0
 
rgb192Author Commented:
thanks
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.