Skip site navigation (1) Skip section navigation (2)

Re: Table-design for categories, suggestions needed

From: Richard Broersma <richard(dot)broersma(at)gmail(dot)com>
To: Rikard Bosnjakovic <rikard(dot)bosnjakovic(at)gmail(dot)com>
Cc: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Table-design for categories, suggestions needed
Date: 2010-08-06 04:42:40
Message-ID: A835E918-24D8-4AE1-BA43-9B04C1EF3DAA@gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
On Aug 5, 2010, at 3:50 PM, Rikard Bosnjakovic 
> 
> Perhaps a better example is an Ebay-listing. For a random page I have
> open right now I see at the top: "Listed in category: Mobile & Home
> Phones > Mobile Phone Accessories > Other Mobile Phone Accessories >
> Other Mobile Phone Accessories".
> 
> This "chain" is what I'm trying to duplicate.


Okay, then what you are trying to model is a tree.

Postgresql has a special contrib module called Ltree that manages this nicely.

Other than this, there are three data models that you can choose from:

Adjacency list model Aka BOM model
Path enumeration model
Nested set model

The first model is probably the easiest to grasp.  And since pg 8.4 supports recursive queries this model becomes a nice choice. 

Sent from my iPod

In response to

pgsql-novice by date

Next:From: Carel CombrinkDate: 2010-08-06 13:36:40
Subject: Libpq in C Language Function
Previous:From: Leon StarrDate: 2010-08-06 04:21:00
Subject: Best way to test/develop lots of nested functions?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group