?
Solved

assign a category to products table

Posted on 2012-09-05
11
Medium Priority
?
356 Views
Last Modified: 2012-09-16
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
Comment
Question by:rgb192
  • 6
  • 5
11 Comments
 
LVL 2

Expert Comment

by:exoduster
ID: 38371504
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
 
LVL 1

Author Comment

by:rgb192
ID: 38371761
how to make sure
categoryfoldersproductid has only one row per productid

and which table to add primary/foreign keys
how
0
 
LVL 2

Expert Comment

by:exoduster
ID: 38372194
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 2

Expert Comment

by:exoduster
ID: 38372370
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
 
LVL 1

Author Comment

by:rgb192
ID: 38372969
one (category) to many (products)
0
 
LVL 2

Expert Comment

by:exoduster
ID: 38375473
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
 
LVL 1

Author Comment

by:rgb192
ID: 38376829
>>
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
 
LVL 2

Expert Comment

by:exoduster
ID: 38382426
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
 
LVL 1

Author Comment

by:rgb192
ID: 38387556
>>
,,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
 
LVL 2

Accepted Solution

by:
exoduster earned 2000 total points
ID: 38390060
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
 
LVL 1

Author Closing Comment

by:rgb192
ID: 38404406
thanks
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question