Received: from rakete.joerdens.de (rakete.joerdens.de [62.104.116.200]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBDFmLp17206 for ; Wed, 13 Dec 2000 10:48:21 -0500 (EST) (envelope-from frank@joerdens.de) Received: from limedes.archi-me-des.de (pD900250B.dip.t-dialin.net [217.0.37.11]) by rakete.joerdens.de (8.9.3/8.9.3) with ESMTP id RAA29863 for ; Wed, 13 Dec 2000 17:07:28 +0100 Received: from joerdens.de (tricky.archi-me-des.de [192.168.100.1]) by limedes.archi-me-des.de (8.9.3/8.9.3) with ESMTP id QAA09513 for ; Wed, 13 Dec 2000 16:33:27 +0100 Message-ID: <3A379A5F.97FFAF01@joerdens.de> Date: Wed, 13 Dec 2000 16:48:47 +0100 From: Frank Joerdens X-Mailer: Mozilla 4.72 [en] (WinNT; I) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: How to represent a tree-structure in a relational database Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200012/106 X-Sequence-Number: 439 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