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

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
>