Re: Table Design for Hierarchical Data

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

In response to

Responses

Browse pgsql-sql by date

  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