Received: from hostbureau.hostbureau.net (hostbureau.hostbureau.net [209.150.128.194] (may be forged)) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBDJjnX24510 for ; Wed, 13 Dec 2000 14:45:49 -0500 (EST) (envelope-from mig@utdt.edu) Received: from ant.utdt (acad.utdt.edu [200.41.62.131]) by hostbureau.hostbureau.net (8.8.5/8.8.5) with SMTP id NAA17759; Wed, 13 Dec 2000 13:45:17 -0600 From: miguel sofer Date: Wed, 13 Dec 2000 19:44:02 GMT Message-ID: <20001213.19440200@ant.utdt> Subject: Re: How to represent a tree-structure in a relational database To: Frank Joerdens CC: pgsql-sql@postgresql.org In-Reply-To: <3A379A5F.97FFAF01@joerdens.de> References: <3A379A5F.97FFAF01@joerdens.de> X-Mailer: Mozilla/3.0 (compatible; StarOffice/5.2;Linux) X-Priority: 3 (Normal) MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by mail.postgresql.org id eBDJjpX24519 X-Archive-Number: 200012/110 X-Sequence-Number: 444 I once started writing a small paper on this subject; it is still in a rather preliminary state. You can download the draft (and some ill documented code, 53kB) from http://www.utdt.edu/~mig/sql-trees Miguel >>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<< On 12/13/00, 12:48:47 PM, Frank Joerdens wrote regarding [SQL] How to represent a tree-structure in a relational database: > 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? > - Frank