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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
