Re: Categories and Sub Categories (Nested)

Martin Kuria wrote:
> Hi,
>
> I have a postgresql database Table Categories which has the structure
> like this
>
> Cat_ID | Parent_ID | Name
> ------------------------------------
> 1 | 0 | Automobiles
> 2 | 0 | Beauty & Health
> 3 | 1 | Bikes
> 4 | 1 | Cars
> 5 | 3 | Suzuki
> 6 | 3 | Yamaha
> 7 | 0 | Clothes

This has one big drawback, you'll need a query for (almost) every record
you want to select. Some databases have "solutions" for this, varying in
usability (though I really have only used one such database so far).

> My question is what is the advantage of Creating NESTED Table over have
> a table structure below which achieve the same goal:

In relational databases? None, AFAIK. Drawbacks seems more like it.

Fabian Pascal describes a method in one of his books that works by
exploding the tree. Ideally this should be done "automagically" by the
database; he suggests an EXPLODE function that takes a table as
argument, but I'm quite confident the same can be achieved with a few
triggers. It works almost as what you describe in your second solution.

>>> Category Table
>
>
> Cat_ID | Cat_Name
> ------------------------------------
> 1 | Automobiles
> 2 | Beauty & Health
> 3 | Bikes
> 4 | Cars
> 7 | Clothes

>>> Subcategory Table

You could use a relation-table here, and put the subcategories in the
category table. That table would look like:

Category_Category table
Cat_Id | Parent_Id | Depth
-----------------------------
3 | 1 | 1
4 | 1 | 1
5 | 3 | 1
5 | 1 | 2
6 | 3 | 1
6 | 1 | 2

Note that all descendents have relations to all their ancestors. That's
what makes this thing work. Automatically keeping track of those can be
done with triggers on insert, update and delete.

Now you can query all children of automobiles at once:

select category.*, categore_category.parent_id, category_category.depth
from category inner join category_category on (cat_id = parent_id)
where parent_id = 1;

Cat_Id | Cat_Name | Depth
-------------------------------
3 | Bikes | 1
4 | Cars | 1
5 | Suzuki | 2
6 | Yamaha | 2

You can add more columns with specific data that can be used to sort the
tree, for example by keeping a reference to the direct parent.

Regards,

--
Alban Hertroys
alban(at)magproductions(dot)nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //