From: Michael Glaesemann <grzm@seespotcode.net>
To: Lee Hachadoorian <lee.hachadoorian@gmail.com>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Table Design for Hierarchical Data
Date: Tue, 6 Apr 2010 18:04:54 -0400
Message-ID: <F04AC8F6-46C5-48AD-8741-D185F5F9B82F@seespotcode.net> (raw)
In-Reply-To: <n2i5ab13581004061033j4ed25734xd0504cb95a1fc092@mail.gmail.com>
References: <n2i5ab13581004061033j4ed25734xd0504cb95a1fc092@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: grzm@seespotcode.net, lee.hachadoorian@gmail.com
Subject: Re: Table Design for Hierarchical Data
In-Reply-To: <F04AC8F6-46C5-48AD-8741-D185F5F9B82F@seespotcode.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox