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
>