Designing category system

Hi,

I am trying to design tables for managing category system.

In my inventory management system, I have a receive form, where I have to enter a product from a subcategory of a category.

I can't figuire out how should i design my tables. Am I correct with these tables -

Products Table - ProdID, ProdName, CatID, SCatID
Category Table - CatID, CatName
SubCategory Table - SCatID, SCatName

I can't show only the subCategories associated with a Category. What will be the design?

Also in the Receive form, which one will be logical? Should I select a category first, and related subCategories appears, and then product list form that subcategory appear?

Or should I select the product first, then categories and subCategories should appear?

Thanx a lot in advance for your help.

Regards
Kapalic

It depends how your hierarchy is,
suppose if your hierarchy follows ,

Category
|- Sub-Category
|- Product

Then you can go for following design,

Category -> CatId, CatName, ParentCatId
Where
CatId Primary key
ParentCatId foreign Key of CatId

(if ParentCatid is null then Category otherwise Sub Category)

Product -> ProductId, CatId
Where
CatId is Foregitn key of Category(CatId)

Regarding selection,
Product -> Sub-Category is ONE ON ONE relation
Sub-Cateogry -> Category is ONE ON ONE Relation

But,
Category -> Sub-Category is ONE To MANY relation
Sub-Cateogry -> Product is ONE ON MANY relation

Again its depends how your business rules defines.

|||

Hi ManiD,

Thnx for your reply! I will use the table structure u provided. This is much more logical.

And regarding to choosing fields, i do not have any binding other than existance of category and subcategory id. I just want an expert openion on which approach will be better for the users and developers perspective.

Regards
Kapalic