Received: from serv1.ilse.nl (serv1.ilse.nl [212.189.131.11]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBDJM6X19536 for ; Wed, 13 Dec 2000 14:22:06 -0500 (EST) (envelope-from mathijs@ilse.nl) Received: (from mathijs@localhost) by serv1.ilse.nl (8.11.0/8.10.2) id eBDJMO262139; Wed, 13 Dec 2000 20:22:24 +0100 (MET) Date: Wed, 13 Dec 2000 20:22:24 +0100 From: Mathijs Brands To: Frank Joerdens Cc: pgsql-sql@postgresql.org Subject: Re: How to represent a tree-structure in a relational database Message-ID: <20001213202224.C61747@ilse.nl> References: <3A379A5F.97FFAF01@joerdens.de> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5i In-Reply-To: <3A379A5F.97FFAF01@joerdens.de>; from frank@joerdens.de on Wed, Dec 13, 2000 at 04:48:47PM +0100 X-Unexpected-Header: The Spanish Inquisition X-Archive-Number: 200012/109 X-Sequence-Number: 443 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)