Thread: Categories and Sub Categories (Nested)

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

According to the above Database data, Suzuki and Yamaha are the
Subcategories of Category Bikes and Bikes in turn is the Subcategory of
Automobiles.

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

>>Category Table

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

>>Subcategory Table

Subcat_ID | Sub_Cat_ID | Sub_Name
------------------------------------------------------
10 | 3 | Suzuki
11 | 3 | Yamaha
12 | 1 | Bikes
13 | 1 | Cars

Since all display the data in them in a tree structure like below

Automobiles
---> Bikes
------> Suzuki
------> Yamaha
---> Cars
Beauty & Health
Clothes

Please advice should I Create a Nested Table Categories or
Should I create TWO Tables, Category Table and Subcategory Table?

Thanks once again.
+-----------------------------------------------------+
| Martin W. Kuria (Mr.) martin.kuria@unon.org
+----------------------------------------------------+

_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/

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@magproductions.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 //

You should look up the contrib module ltree which is made for this
sort of thing.  If you reinvent the wheel like this, you will be stuck
with 2 levels.  With ltree you can have as many as you need and add
more at any time.  It lets you query for ancestors and descendants of
any item at any level.

I highly recommend it.

On 4/19/06, Martin Kuria <martinkuria@hotmail.com> 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
>
> According to the above Database data, Suzuki and Yamaha are the
> Subcategories of Category Bikes and Bikes in turn is the Subcategory of
> Automobiles.
>
> My question is what is the advantage of Creating NESTED Table over have a
> table structure below which achieve the same goal:
>
> >>Category Table
>
> Cat_ID | Cat_Name
> ------------------------------------
> 1 | Automobiles
> 2 | Beauty & Health
> 3 | Bikes
> 4 | Cars
> 7 | Clothes
>
> >>Subcategory Table
>
> Subcat_ID | Sub_Cat_ID | Sub_Name
> ------------------------------------------------------
> 10 | 3 | Suzuki
> 11 | 3 | Yamaha
> 12 | 1 | Bikes
> 13 | 1 | Cars
>
> Since all display the data in them in a tree structure like below
>
> Automobiles
> ---> Bikes
> ------> Suzuki
> ------> Yamaha
> ---> Cars
> Beauty & Health
> Clothes
>
> Please advice should I Create a Nested Table Categories or
> Should I create TWO Tables, Category Table and Subcategory Table?
>
> Thanks once again.
> +-----------------------------------------------------+
> | Martin W. Kuria (Mr.) martin.kuria@unon.org
> +----------------------------------------------------+
>
> _________________________________________________________________
> Express yourself instantly with MSN Messenger! Download today it's FREE!
> http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

hi,

I have a problem with my postgresql database it always gives me an error:

Warning: pg_connect() [function.pg-connect]: Unable to connect to PostgreSQL
server: FATAL: sorry, too many clients already in ~/includes/DbConnector.php
on line 27

Please advice how I can manage my database to handle enough connections
please advice.

+-----------------------------------------------------+
| Martin W. Kuria (Mr.) martin.kuria@unon.org
+----------------------------------------------------+

_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/

Martin,

please check out the server configuration documentation at

http://www.postgresql.org/docs/8.1/interactive/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-SETTINGS

your configuration file of choice is postgresql.conf within the data directory.

(btw: because the default is rather big, it is very likely that there is some "not reusing connections" or "not closing connection when ready with usage" bug in your application)

best wishes,

Harald




--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
PostgreSQL - supported by a community that does not put you on hold

Look in data/postgres.conf

You will find a line there such as max_connections = 50 you will also need
to check that shared_buffers is at least twice the max_connections.

You can also start Postmaster with -B nBuffers -N maxbackends


On 20/4/2006 17:41, "Martin Kuria" <martinkuria@hotmail.com> wrote:

> hi,
>
> I have a problem with my postgresql database it always gives me an error:
>
> Warning: pg_connect() [function.pg-connect]: Unable to connect to PostgreSQL
> server: FATAL: sorry, too many clients already in ~/includes/DbConnector.php
> on line 27
>
> Please advice how I can manage my database to handle enough connections
> please advice.
>
> +-----------------------------------------------------+
> | Martin W. Kuria (Mr.) martin.kuria@unon.org
> +----------------------------------------------------+
>
> _________________________________________________________________
> Express yourself instantly with MSN Messenger! Download today it's FREE!
> http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

Ian Harding wrote:
> You should look up the contrib module ltree which is made for this
> sort of thing.  If you reinvent the wheel like this, you will be stuck
> with 2 levels.  With ltree you can have as many as you need and add
> more at any time.  It lets you query for ancestors and descendants of
> any item at any level.
>
> I highly recommend it.

Ah, one of those modules I still need to investigate. It looks promising.

Can it do networked structures? That'd be kind of hard with an index on
a path when a node can have multiple parent nodes, I figure...

--
Alban Hertroys
alban@magproductions.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 //

You may also want to look into your php.ini settings.

pgsql.max_persistent pgsql.max_links pgsql.auto_reset_persistent and
PGSQL_CONNECT_FORCE_NEW are options you would want to check into.


> Look in data/postgres.conf
>
> You will find a line there such as max_connections = 50 you will also need to
> check that shared_buffers is at least twice the max_connections.
>
> You can also start Postmaster with -B nBuffers -N maxbackends
>
>
> On 20/4/2006 17:41, "Martin Kuria" <martinkuria@hotmail.com> wrote:
>
>> hi,
>>
>> I have a problem with my postgresql database it always gives me an error:
>>
>> Warning: pg_connect() [function.pg-connect]: Unable to connect to PostgreSQL
>> server: FATAL: sorry, too many clients already in ~/includes/DbConnector.php
>> on line 27
>>
>> Please advice how I can manage my database to handle enough connections
>> please advice.
>>
>> +-----------------------------------------------------+
>> | Martin W. Kuria (Mr.) martin.kuria@unon.org
>> +----------------------------------------------------+
>>
>> _________________________________________________________________
>> Express yourself instantly with MSN Messenger! Download today it's FREE!
>> http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: explain analyze is your friend
>>
>

On Apr 19, 2006, at 14:44 , Martin Kuria wrote:

> 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
>
> According to the above Database data, Suzuki and Yamaha are the
> Subcategories of Category Bikes and Bikes in turn is the
> Subcategory of Automobiles.

If you're looking at having a potentially deep hierarchy, I'd
recommend taking a look at nested sets, which also allows you to keep
everything in one table.

http://www.intelligententerprise.com/001020/celko.jhtml?
_requestid=145525]

By the way, the method you outline above is often called the
adjacency list model.

Hope this helps.

Michael Glaesemann
grzm myrealbox com