From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Organization: Dynacom Tankers Mgmt
To: pgsql-sql@postgresql.org
Subject: Re: Table Design for Hierarchical Data
Date: Wed, 7 Apr 2010 09:00:14 +0200
User-Agent: KMail/1.9.7
References: <n2i5ab13581004061033j4ed25734xd0504cb95a1fc092@mail.gmail.com>
In-Reply-To: <n2i5ab13581004061033j4ed25734xd0504cb95a1fc092@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <201004071000.15529.achill@matrix.gatewaynet.com>

You could also consider the genealogical approach, e.g.


postgres@dynacom=3D# \d paintgentypes
                                  Table "public.paintgentypes"
 Column  |   Type    |                                 Modifiers
=2D--------+-----------+---------------------------------------------------=
=2D-----------------------
 id      | integer   | not null default nextval(('public.paintgentypes_id_s=
eq'::text)::regclass)
 name    | text      | not null
 parents | integer[] |
Indexes:
    "paintgentypes_pkey" PRIMARY KEY, btree (id)
    "paintgentypes_name2" UNIQUE, btree (name) WHERE parents IS NULL
    "paintgentypes_uk" UNIQUE, btree (name, parents)
    "paintgentypes_first" btree (first(parents))
    "paintgentypes_last" btree (last(parents))
    "paintgentypes_level" btree (level(parents))
    "paintgentypes_name" btree (name)
    "paintgentypes_parents" gin (parents gin__int_ops)

The indexes are based on the contrib/intarray package.
It is very fast to do any operation on this tree.
Also it is very fast to search for the parent of any node, or the
children of any node, or the whole subtree of any node, or the depth of any=
 node in the tree.

The parents of any node to the root, i.e. the path of any node to the root =
are depicted as
parents[0] : immediate parent
parents[1] : immediate parent of the above parent
=2E....
parents[n] : root of the tree

=CE=A3=CF=84=CE=B9=CF=82 Tuesday 06 April 2010 20:33:18 =CE=BF/=CE=B7 Lee H=
achadoorian =CE=AD=CE=B3=CF=81=CE=B1=CF=88=CE=B5:
> Please point me to another listserv or forum if this question is more
> appropriately addressed elsewhere.
>=20
> I am trying to come up with a structure to store employment data by NAICS
> (North American Industrial Classification System). The data uses a
> hierarchical encoding scheme ranging between 2 and 5 digits. That is, each
> 2-digit code includes all industries beginning with the same two digits. =
61
> includes 611 which includes 6111, 6112, 6113, etc. A portion of the
> hierarchy is shown after the sig.
>=20
> A standard way to store hierarchical data is the adjacency list model, wh=
ere
> each node's parent appears as an attribute (table column). 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).
>=20
> One way to store this data would be to store at the most granular level
> (5-digit NAICS) and then aggregate up if I wanted employment at the 4-, 3=
=2D,
> or 2-digit level. The problem is that because of nondisclosure rules, the
> data is sometimes censored at the more specific level. I might, for examp=
le,
> have data for 6114, but not 61141, 61142, 61143. For a different branch of
> the tree, I might have data at the 5-digit level while for yet another
> branch I might have data only to the 3-digit level (not 4 or 5). I think
> that means I have to store all data at multiple levels, even if some of t=
he
> higher-level data could be reconstructed from other, lower-level data.
>=20
> 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)? =
If
> one table, should the digits be broken into separate columns? Should pare=
nt
> ids be stored in each node?
>=20
> More generally, what questions should I be asking to help decide what
> structure makes the most sense? Are there any websites, forums, or books
> that cover this kind of problem?
>=20
> Regards,
> --Lee
>=20


=2D-=20
Achilleas Mantzios