tags:

views:

38

answers:

3

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

A: 

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    
AllenG
I would have had a table Products (ID, Description, CategoryID)
Sjuul Janssen
@Sjuul Janssen: But then you're repeating product descriptions if a single product can be in multiple categories. For instance, what if "Children Handles" is also in the category "Child Safety Hardware?" or somesuch? It also denotes a Parent-Child relationship whereas I see this as much more of a sibling relationship.
AllenG
+1  A: 

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)
Michael Pakhantsov
A: 

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.

Philip Kelley