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

Designing Product, category database table

Hello.
I have 2 question on designing DB with MS-SQL 2005.

1. I am trying to create few database table for products.
I am stuck on how to copy same product to multiple sub categories.
For Example, what do I do if I want to display ProductID 001 to subcategoryID AAA and subcateogry BBB ?

These are the samples of my tables

Category Table
-CategoryID (PK)
-Name

SubCateogry Table
-SubCateogryID (PK)
-CategoryID (FK)
-Name

Product Table
-ProductID (PK)
-SubcategoryID (FK)
-Name


2. With above db, I can't create other subcateogry under subcategory. If I wanted to have more categories under subcategories, How should I design the DB?

Thank you!
0
erin027
Asked:
erin027
  • 3
1 Solution
 
dportasCommented:
There are several possible answers. Modeling trees and hierarchies is a huge topic and you will find a great many examples online or in books.

The following is one simple example:

CREATE TABLE Category
(CategoryID INT NOT NULL PRIMARY KEY,
 ParentCategoryID INT  DEFAULT (0) NOT NULL REFERENCES Category (CategoryID),
 CategoryName VARCHAR(50) NOT NULL);

INSERT INTO Category VALUES (0,0,'<Root>');
INSERT INTO Category VALUES (1,0,'Category A');
INSERT INTO Category VALUES (2,0,'Category B');
INSERT INTO Category VALUES (3,1,'Sub Category A1');
INSERT INTO Category VALUES (4,1,'Sub Category A1');
0
 
erin027Author Commented:
I do not need SQL. I need the table design...
Specially, for question 1.
Thank you.
0
 
dportasCommented:
Category
{CategoryID [PK],
 ParentCategoryID [FK],
 CategoryName}

Hope that helps.
0
 
dportasCommented:
Regarding Q1. The solution is to create a new table:

ProductSubCategory
{ProductID [FK],
 SubCategoryID [FK]}

(ProductID, SubCategoryID) is the PK of this table.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Based on your requirements you may use the following hierchical represention in relational terms...

Category: name(PK, NN)
Lineage: category_parent(FK --> Category), category_child(FK --> Category)  PK being the composite key {category_parent+category_child}
Product_category: product_name(FK --> name in Product), category_name(FK --> Category)
Product: name(PK), price(+ other product attributes)

Now suppose the following content for your table...

Category:
name
----------------------------------------
Category A
Category B
SubCategory A1
SubCategory A2
SubCategory B1
SubCategory B2
SubCategory B3

Lineage:
category_parent            category_child
----------------------------------------
Category A            SubCategory A1
Category A            SubCategory A2
Category B            SubCategory B1
Category B            SubCategory B2
Category B            SubCategory B3

Product_category
product_name            category
----------------------------------------
P A1                  SubCategory A1
P A2                  SubCategory A2
P B1                  SubCategory B1
P B2                  SubCategory B2
P B3                  SubCategory B3


Product:
name
----------------------------------------
P A1
P A2
P B1
P B2
P B3


Question1: what do I do if I want to display ProductID 001 to subcategoryID AAA and subcateogry BBB ?
Answer 1: with the above design you simply do 1 insert in product_category


Question 2: With above db, I can't create other subcateogry under subcategory. If I wanted to have more categories under subcategories, How should I design the DB?
Answer 2: with above design you may create as many subcategories as you want.  All you have to do is insert 1 line in category and 1 line in Lineage table (at the time you create the category).

Hope this helps...
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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