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@xxxxxxxxxxx> 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@xxxxxxxx > +----------------------------------------------------+ > > _________________________________________________________________ > 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 >
