asked on

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!

dportas

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');

erin027

ASKER

I do not need SQL. I need the table design...
Specially, for question 1.
Thank you.

dportas

Category
{CategoryID [PK],
ParentCategoryID [FK],
CategoryName}

Hope that helps.

dportas

Regarding Q1. The solution is to create a new table:

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

(ProductID, SubCategoryID) is the PK of this table.

ASKER CERTIFIED SOLUTION

Racim BOUDJAKDJI

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial