Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 3BF0D632315 for ; Tue, 6 Apr 2010 19:28:51 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 76589-02 for ; Tue, 6 Apr 2010 22:28:40 +0000 (UTC) X-Greylist: delayed 00:23:31.407803 by SQLgrey-1.7.6 Received: from proton.planetargon.com (proton.planetargon.com [198.145.115.11]) by mail.postgresql.org (Postfix) with ESMTP id C0C68634DAC for ; Tue, 6 Apr 2010 19:28:39 -0300 (ADT) Received: by proton.planetargon.com (Postfix, from userid 502) id 6F24182CDD1; Tue, 6 Apr 2010 15:05:07 -0700 (PDT) Received: from [192.168.1.150] (unknown [68.64.144.221]) by proton.planetargon.com (Postfix) with ESMTP id D5BBD82CDC2; Tue, 6 Apr 2010 15:05:02 -0700 (PDT) Subject: Re: Table Design for Hierarchical Data Mime-Version: 1.0 (Apple Message framework v1078) Content-Type: text/plain; charset=us-ascii From: Michael Glaesemann In-Reply-To: Date: Tue, 6 Apr 2010 18:04:54 -0400 Cc: pgsql-sql Content-Transfer-Encoding: quoted-printable Message-Id: References: To: Lee Hachadoorian X-Mailer: Apple Mail (2.1078) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.11 tagged_above=-10 required=5 tests=BAYES_05=-1.11 X-Spam-Level: X-Archive-Number: 201004/10 X-Sequence-Number: 33937 On Apr 6, 2010, at 13:33 , Lee Hachadoorian wrote: > A standard way to store hierarchical data is the adjacency list model, = where > each node's parent appears as an attribute (table column). Another is nested sets which performs quite nicely for loads which are = more read than write (which I suspect is the case here). > So 6111 would > list 611 as its parent. Since NAICS uses a hierarchical encoding = scheme, the > node's name is the same as the node's id, and the parent can always be > derived from the node's id. Storing the parent id separately would = seem to > violate a normal form (because of the redundancy). I'd consider the code a representation of the node structure rather than = the implementation of the node structure. > The problem is that because of nondisclosure rules, the > data is sometimes censored at the more specific level. I don't know if this is per-user or per-category or what, but it may be = something you store separately from the main table. > Specifically I'd like to know if this should be a single table or = should > there be a separate table for each level of the hierarchy (four in = all)? I'd say one table for hierarchy and possibly another for the permissions = data. > If one table, should the digits be broken into separate columns? Probably not. > Should parent > ids be stored in each node? Only if you use an encoding scheme (such as adjacency list) which = requires it. Michael Glaesemann grzm seespotcode net