how to create database for business site containing no of subcategories

[greeny_1984]

Hi everyone,
Ima working on a business site similar to alibaba.com.In that we have a main categories
named
agriculture,chemicals ....so on.
on clicking on agriculture we get one more subcategory containing different names line
plant products,beans so on..

on clicking on beans we get the companies selling beans.

similar to that we want to create a website containing categories,subcategories.
the problem is that we dont no exactly how many no of subcategories will be present.
I want dynamically create subcategories.Can any PLZ give me the database design for this problem.

waiting for u r replies

[tesuji]

Hi greeny,

a way to solve this completely independently from the number of categories and subcategories are (recursive) trees in SQL. There is a famous book: Transact-SQL Cookbook by Ales Spetic, Jonathan Gennick what has an extra chapter only dealing with that topic. They also explain how to traverse trees recursively by means of transact SQL.

Below table I once designed for a similar problem you are facing with is based on an example from this book. Our categories table (it slightly differs from below simple example) works great, and the table now has over 4000 categories and subcategories, still increasing.

create table categories (    -- father of main knot is NULL
ucat integer null,    -- a father knot of a tree, it has many son knots
lcat integer not null,     -- the son knot. has only one father knot
name varchar (30), 
primary key (lcat),
foreign key (ucat) references categories);   -- recursive definition

-- Some samples
insert into categories values (null,1,'Categories');
insert into categories values (1,2,'Software');
insert into categories values (1,3,'Hardware');
insert into categories values (1,4,'Network');
insert into categories values (2,5,'Linux');
insert into categories values (3,6,'PC');
insert into categories values (3,7,'Printer');
insert into categories values (6,8,'LCD');
commit

-- for example select all subcategories of category hardware
select * from categories
where ucat = all (select lcat from categories
where name = 'Hardware');

/*  Result
ucat   lcat   name
------------------
3      6      PC
3      7      Printer
*/

You may draw the tree to see how it works.
Happy treeing!

krs,
tesu

[greeny_1984]

Hi tesu,
Thanks for the reply.I have some doubts here in the example u gave.U gave code for a single category and single sub category.
Can u explain u r code for this example

Agriculture--->main category
|
V
plant products-->sub category
|
V
Beans-->sub sub category
|
V
green beans-->sub sub sub category
|
V
companies containg this product--->final result

waiting for u r reply

[tesuji]

Hi greeny,

There is no doubt that your doubt tells me you didn't understood anything of my sample code
neither you have drawn the tree of the example categories tree. If you don't understand this code, especially the primary, foreign keys and the insert statements, you may freely ask me.

First, I would advice you that you try to draw the categories tree from the given insert statements. Then compare it with your green bean example. What will you assess then? Yes, this very plain categories tree is already much more complex than your green bean example.

krs,
tesu

[tesuji]

Sorry, corretion:

Hi greeny,
... Our categories table (it slightly differs from below simple example) works great, and the table now has over 4000 categories and subcategories, still increasing...
tesu

We don't have 4000, sorry, only about 400 categories!

Need a little help for drawing the sample tree: it consists of one main category, three sub categories, two sub sub categories, and one sub sub sub category.

krs,
tesu

[greeny_1984]

Hi tesu,
Thanks for the replies.I have understood what u have said and added a column named level
to get the level of category.
CAT_ID
CAT_NAME
CAT_DESC
CAT_LEVEL
PARENT_CAT
and its working great.

[tesuji]

hi, I am glad that the categories tree now also works fine for you. You are absolutely right when adding a level because navigation is then sometimes easier. In my actual tree I have also a further category for categorizing the categories, you see? Additionally to the product data categorized by the leaf nodes there is a further table with attributes to gather common data of a product.

krs,
tesu

[tesuji]

Hi tesu,
Thanks for the replies.I have understood what u have said and added a column named level
to get the level of category.
CAT_ID
CAT_NAME
CAT_DESC
CAT_LEVEL
PARENT_CAT
and its working great.

Just out of curiosity, please post the CREATE TABLE statement of your categories tree.

krs,
tesu