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
casehere).
> 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