Re: Table Design for Hierarchical Data

Lee Hachadoorian wrote:
>
> 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.From the http://www.census.gov/eos/www/naics/ website:

"NAICS is a two- through six-digit hierarchical classification system, 
offering five levels of detail. Each digit in the code is part of a 
series of progressively narrower categories, and the more digits in the 
code signify greater classification detail. The first two digits 
designate the economic sector, the third digit designates the subsector, 
the fourth digit designates the industry group, the fifth digit 
designates the NAICS industry, and the sixth digit designates the 
national industry. The five-digit NAICS code is the level at which there 
is comparability in code and definitions for most of the NAICS sectors 
across the three countries participating in NAICS (the United States, 
Canada, and Mexico). The six-digit level allows for the United States, 
Canada, and Mexico each to have country-specific detail. A complete and 
valid NAICS code contains six digits."

I think I'd be inclined to store it as defined above with tables for 
sector, subsector, industry-group and NAICS-industry. So the NAICS table 
might have a primary key of industry_code (11131, Orange Groves) and a 
industry_group column with a foreign-key constraint to the 
industry-group table (1113, Fruit and Tree Nut  Farming). You might add 
a constraint to ensure that the industry-group is the appropriate 
substring of the naics code and so on up the heirarchy. If you are 
dealing with importing a large amount of static source data for 
analysis, these tables will also be tailor-made places to do 
pre-aggregation.

Adjacency lists work well in certain cases where the depths of the trees 
are variable or indeterminate. For example, think of an employee->boss 
org-chart for a large company. The maintenance supervisor for an area 
might be a dozen levels below the CEO and be a several levels above the 
branch night janitor while the CEO's personal assistant is just one 
level down but with no direct reports. The CTE/recursive-query features 
in 8.4 are great for this. But in the case you have described, the 
number of levels is well defined as is the type of information 
associated with each level.

But this all depends on the nature of your source data, how often it is 
updated, how big it is and the questions you want answered. It might be 
perfectly acceptable to just have the 5-digit code on all your 
individual data records and do something like select ... group by 
substr(full_naics_code,1,3) where substr(full_naics_code,1,2)='61'). In 
this case you will still want to keep the NAICS definition table 
separate and link to it.

One question that might impact this is the coding of your source data. 
Is it all full 5-digit coding or are some records coded at a high level 
of detail and others only to the top-level?

>
> 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.
>
What do you mean by censored? Is the data supplied to you pre-aggregated 
to some level and censored to preserve confidentiality or are do you 
have the record-level source data and the responsibility to suppress 
data in your reports? Is the data suppression ad-hoc (i.e. someone comes 
to you and says don't display these five aggregates), based on simple 
rules (don't display any aggregate with fewer than 15 records) or on 
more complex rules (don't display any data that would allow calculation 
of a group of fewer than 15)? My guess is that the multi-table scenario 
will be better suited to flagging aggregates for suppression.

Cheers,
Steve