pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mathijs Brands <mathijs@ilse.nl>
To: Frank Joerdens <frank@joerdens.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to represent a tree-structure in a relational database
Date: Wed, 13 Dec 2000 20:22:24 +0100
Message-ID: <20001213202224.C61747@ilse.nl> (raw)
In-Reply-To: <3A379A5F.97FFAF01@joerdens.de>
References: <3A379A5F.97FFAF01@joerdens.de>

On Wed, Dec 13, 2000 at 04:48:47PM +0100, Frank Joerdens allegedly wrote:
> I am just thinking about the data model for a little content management system that I am
> currently planning. Individual articles are sorted under different categories which branch
> into subcategories, sub-subcategories etc. up to a depth of about 6 or 7 levels. The
> structure should be extensible, i.e. it must be possible to add levels. What I am thinking
> now is that you would keep the index in a separate index table (linked with the primary
> key in the articles table), which would have 6 or 7 fields initially, and that you'd add
> columns with the alter table command, if need be, to make the structure deeper. Is this
> the recommended way to go about it? It feels pretty 'right' to me now but since the
> problem should be fairly common, there must be other people who have thought and written
> about it and there might even be a recognized 'optimal' solution to the problem.
> 
> Comments?

Yeah. I've built something similar.

The way I've done it:
  Give each record a unique ID (generated with a sequence) and store
  the records in a table. Create a second table in which you store
  parent id-child id combinations.

  So:

    1 - Automotive transport
    2 - Cars
    3 - Motorcycles

    Store in the table:
      1-2
      1-3

  There's one main category (Automotive transport) which has two sub-categories:
    Cars & Motorcyles

The way I'd do it if I had to do it again:
  Give each record a unique id, generated by the application. Denote levels with
  extra letters.

  So:

   AA   - Automotive transport
   AAAA - Cars
   AAAB - Motorcycles

  The structures has the added bonus of making it very easy to determine all the
  sub-categories of a category, no matter how deep the tree is below the category
  you're looking at. With the first approach it is not possible to do this in a
  single SQL query. You could do this with a function, I guess.

I hope this is of some use to you.

Cheers,

Mathijs
-- 
"Borrowers of books -- those mutilators of collections, spoilers of the
 symmetry of shelves, and creators of odd volumes." 
        Charles Lamb (1775-1834) 



view thread (27+ messages)  latest in thread

Message-ID: <20001213202224.C61747@ilse.nl>
Permalink:  ../20001213202224.C61747@ilse.nl/
Also on:    postgresql.org/message-id/20001213202224.C61747@ilse.nl

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: mathijs@ilse.nl, frank@joerdens.de
  Subject: Re: How to represent a tree-structure in a relational database
  In-Reply-To: <20001213202224.C61747@ilse.nl>
  References: <3A379A5F.97FFAF01@joerdens.de> <20001213202224.C61747@ilse.nl>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox