Received: from eater.zotnet.com (IDENT:root@snapuser87.pacificcrest.net [216.36.24.87]) by hub.org (8.9.3/8.9.3) with ESMTP id MAA66500 for ; Thu, 4 Nov 1999 12:40:07 -0500 (EST) (envelope-from zot@zotconsulting.com) Received: from zotconsulting.com (zot@mp.zotnet.com [216.36.24.118]) by eater.zotnet.com (8.8.7/8.8.7) with ESMTP id KAA14313 for ; Thu, 4 Nov 1999 10:03:52 -0800 Sender: zot@eater.zotnet.com Message-ID: <3821C4EF.CE5484E@zotconsulting.com> Date: Thu, 04 Nov 1999 09:39:59 -0800 From: "Zot O'Connor" Organization: Zot Consulting X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.5-15smp i686) X-Accept-Language: en MIME-Version: 1.0 To: postgres sql Subject: Tracking depth question Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit I have a shopping cart (FishCartSQL) and I have a client with many layers of sub categories (at least 4, maybe more). The current system only has 1 subcategory, I am extending it. I am planning on having subcats reference themselves with a scatscat value which will need to point to scatval (a subcat ID). I know that foreign key does not work, and I will accept that for now, but my issue is tracking the depth of the subcat within the DB. I would rather not rely on external SQL (php) to do the job. The problem is that I am not sure I can reference the depth in the table definition. I would like something like this: CREATE TABLE scattab scatval integer DEFAULT NEXTVAL('scatid_SEQ') PRIMARY KEY, scatscat integer FOREIGN KEY(scattab.scatval), scatdepth integer DEFAULT (SELECT scatval from scattab where scatval = scatscat) + 1, ... Now there are a few problems that I see 1) scatscat is not defined yet 2) I don't think I can default to a select 3) I am assuming an atomic value for the SELECT statement Is there a better Clean way? I was thinking a trigger that fires a separate depth table and adds the values of scatval and its depth. Any ideas? -- Zot O'Connor www.ZotConsulting.com www.WhiteKnightHackers.com