DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:date:message-id:subject:from:to:content-type;
	b=j7ukbIIJEx3ZUh9ZVLNz4xeD9pdes1SkYklvMyRXvsbZ5XD+qNkKBCATH5QU+9CIvV
	+n83T91afpbYJTKrEZmA3cRXmkt5pvAVMTx4q7sDs4IMaA2Xolte5kOV9WxtedJCOnUv
	NTozkavUt+Km2MoWJ/p2LfT773zQAZcfVA2oA=
MIME-Version: 1.0
Date: Tue, 6 Apr 2010 13:33:18 -0400
Message-ID: <n2i5ab13581004061033j4ed25734xd0504cb95a1fc092@mail.gmail.com>
Subject: Table Design for Hierarchical Data
From: Lee Hachadoorian <lee.hachadoorian@gmail.com>
To: pgsql-sql <pgsql-sql@postgresql.org>
Content-Type: multipart/alternative; boundary=001485f7d7da5a23e3048394d738

--001485f7d7da5a23e3048394d738
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

Please point me to another listserv or forum if this question is more
appropriately addressed elsewhere.

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.

A standard way to store hierarchical data is the adjacency list model, wher=
e
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, th=
e
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).

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-,
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 example=
,
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 the
higher-level data could be reconstructed from other, lower-level data.

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 parent
ids be stored in each node?

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?

Regards,
--Lee

--=20
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center

A Portion of the NAICS scheme

61    Educational Services
611    Educational Services
6111    Elementary and Secondary Schools
61111    Elementary and Secondary Schools
6112    Junior Colleges
61121    Junior Colleges
6113    Colleges, Universities, and Professional Schools
61131    Colleges, Universities, and Professional Schools
6114    Business Schools and Computer and Management Training
61141    Business and Secretarial Schools
61142    Computer Training
61143    Professional and Management Development Training
etc=85

--001485f7d7da5a23e3048394d738
Content-Type: text/html; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

Please point me to another listserv or forum if this question is more appro=
priately addressed elsewhere.<br><br>I am trying to come up with a structur=
e to store employment data by NAICS (North American Industrial Classificati=
on 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.<br>
<br>A standard way to store hierarchical data is the adjacency list model, =
where each node&#39;s parent appears as an attribute (table column). So 611=
1 would list 611 as its parent. Since NAICS uses a hierarchical encoding sc=
heme, the node&#39;s name is the same as the node&#39;s id, and the parent =
can always be derived from the node&#39;s id. Storing the parent id separat=
ely would seem to violate a normal form (because of the redundancy).<br>
<br>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-=
, 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 exampl=
e, 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 b=
ranch I might have data only to the 3-digit level (not 4 or 5). I think tha=
t means I have to store all data at multiple levels, even if some of the hi=
gher-level data could be reconstructed from other, lower-level data.<br>
<br>Specifically I&#39;d like to know if this should be a single table or s=
hould there be a separate table for each level of the hierarchy (four in al=
l)? If one table, should the digits be broken into separate columns? Should=
 parent ids be stored in each node?<br>
<br>More generally, what questions should I be asking to help decide what s=
tructure makes the most sense? Are there any websites, forums, or books tha=
t cover this kind of problem?<br><br>Regards,<br>--Lee<br><br>-- <br>Lee Ha=
chadoorian<br>
PhD Student, Geography<br>Program in Earth &amp; Environmental Sciences<br>=
CUNY Graduate Center<br><br>A Portion of the NAICS scheme<br><br>61=A0=A0=
=A0 Educational Services<br>
611=A0=A0=A0 Educational Services<br>
6111=A0=A0=A0 Elementary and Secondary Schools<br>
61111=A0=A0=A0 Elementary and Secondary Schools<br>
6112=A0=A0=A0 Junior Colleges<br>
61121=A0=A0=A0 Junior Colleges<br>
6113=A0=A0=A0 Colleges, Universities, and Professional Schools<br>
61131=A0=A0=A0 Colleges, Universities, and Professional Schools<br>
6114=A0=A0=A0 Business Schools and Computer and Management Training<br>
61141=A0=A0=A0 Business and Secretarial Schools<br>
61142=A0=A0=A0 Computer Training<br>
61143=A0=A0=A0 Professional and Management Development Training<br>
etc=85<br>
<br>

--001485f7d7da5a23e3048394d738--