Categories and Sub Categories (Nested)

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.

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 //

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

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

On 4/20/06, 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

--
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:

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.