From: | Michael Glaesemann <grzm(at)seespotcode(dot)net> |
---|---|
To: | Lee Hachadoorian <lee(dot)hachadoorian(at)gmail(dot)com> |
Cc: | pgsql-sql <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Table Design for Hierarchical Data |
Date: | 2010-04-06 22:04:54 |
Message-ID: | F04AC8F6-46C5-48AD-8741-D185F5F9B82F@seespotcode.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Broersma | 2010-04-06 22:48:44 | Re: Table Design for Hierarchical Data |
Previous Message | Ben Morrow | 2010-04-06 18:47:14 | Re: count function alternative in postgres |