CODEWITHSUNDEEP
sqlsql-server
Bora
Bora

Reputation: 127

Getting data for more than 1 categories in SQL Server

There are some categories and its products. But products is used for another categories. For example, There are "metal handles" categories and it is used for "metal handles" but at the same time it is used for "children handles". I should say them that you belong to "metal handles" but you could be in "children handles" too

How do I create these tables?

Thanks

Upvotes: 0

Views: 76

Answers (3)

Philip Kelley
Philip Kelley

Reputation: 40289

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.

Upvotes: 0

Michael Pakhantsov
Michael Pakhantsov

Reputation: 25370

Frankly do not understand that you really need. Here is three possible cases:

Product and Categories - many to many relations

You need three tables for resolve many to many relations

 Categories(Id, CategoryName)
 Products(Id, ProductName)
 CategoryProducts(ProductId, CategoryId)

Hierarchical structure for Categories

Categories(Id, CategoryName, ParentCategoryId)
Products(Id, ProductName, CategoryId)

Map structure for Categories

Categories(Id, CategoryName)
CategoryMap(CategoryId, PartOfCategoryId)
Products(Id, ProductName, CategoryId)

Upvotes: 1

AllenG
AllenG

Reputation: 8190

I believe what you're talking about is an arc relationship. You'd use something like:

Categories    Products     ProductCategories
-----------   ---------    -----------------
Id            Id           Id
Description   Description  ProductId
                           CategoryId

This way, your "Metal Handles" Category could have an Id of (say) 247, and your "Metal Handles" product could be Id 49 while "Children Handles" is 1022, and the rows in ProductCategories would look like this:

Id         ProductId   CategoryId
1          49          247
2          1022        247

Upvotes: 0

Related Questions