Skip site navigation (1) Skip section navigation (2)

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: (view raw, whole thread or download thread mbox)
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


pgsql-sql by date

Next:From: Richard BroersmaDate: 2010-04-06 22:48:44
Subject: Re: Table Design for Hierarchical Data
Previous:From: Ben MorrowDate: 2010-04-06 18:47:14
Subject: Re: count function alternative in postgres

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group