how to create , category, subcategory ,etc ??

[Tony]

Hello.
I am database newbie.. trying to create a database for my new project.
I like to know , how should i creat , Category , Subcategory , subsub
category ........?
I can't figure the table structure for this .
EX: Producttable, can have a category, and multiple nested subcategory.

Sorry if i am not clear, but hope someone could point me in right direction
or give me a link to a tutorial on this subject.

Thanks in advance

 

[Randy]

Tony wrote

Hello.
I am database newbie.. trying to create a database for my new project.
I like to know , how should i creat , Category , Subcategory , subsub
category ........?
I can't figure the table structure for this .
EX: Producttable, can have a category, and multiple nested subcategory.

Sorry if i am not clear, but hope someone could point me in right
direction
or give me a link to a tutorial on this subject.

Thanks in advance

Tony, try to create a table with the following fields:

ID (primary key, autonumber, no-duplicates)
ParentID (Long)
Categoryname (Text)

This structure will allow you to create an unlimited numbers of categories,
subcategories,... Let me know if you have any other concerns.

-Randy

 

[Tony]

Thanks randy for your help.
It seems this is self refrencing table .. so, is this table is the only
table i need to do category and sub and nested sub category? i mean thats
all the attribute i need ?
in order to relate products to this table , do i need to use ID as a FK to
product table ?
the table is working very well. but i do really need to study this table
more and do some practice with it.

Again, thanks a lot

 

[Randy]

Inline replies.

Thanks randy for your help.
It seems this is self refrencing table .. so, is this table is the only
table i need to do category and sub and nested sub category? i mean thats
all the attribute i need ?

Yes. A table with an ID, ParentID and CategoryName is all you need to create
category-subcategory relations, from simple to complex tree-like views, like
in Windows Explorer. The design offers endless subcategories or branches.

in order to relate products to this table, do i need to use ID as a FK to
product table ?

You got it right! The Product table will have a field CategoryID
(referencing a category in the Category table).

the table is working very well. but i do really need to study this table
more and do some practice with it.

Again, thanks a lot

Yes. Work and work the design until you fully understand it. Also here there
are some extra tips:

Depending on your database design needs, you can reduce the complexity of
the Category table by just having two fields: "CategoryName" and
"ParentCategory" (with CategoryName being Primary Key). This has two
advantages: 1) There is no risk of duplicating a category name, since they
will be always unique at all levels of the categories tree. 2) For queries
and reports you won't need to use Inner Joins to decipher the Category name
from a CategoryID, since the ID is the very Category name. This is when
already using referential integrity between the two tables to avoid use of
unlisted categories.

The ID-ParentID-CategoryName design offers more flexibility and uses less
space, but requires some work like not allowing duplicates if there is
another category with the same name and same parent ID. Issue that you can
resolve through code or by setting a new compound-no-duplicates index of
ParentID and CategoryName.

You can also opt to just use a flat (one dimension) category listing, like
in the Northwind sample database (see "Product" and "Categories" forms) from
Microsoft Access. That can be selected from a combobox. Let me know if you
have any other concerns.

-Randy

 

[Tony]

Thanks Randy.
You have been a great help

Best regards.