All:
Bathroom reading, perhaps.
Last week's thread dealt with the question of how one might process a
BOM of any depth; but the introduction of 'nested sets' and Karen's use
yesterday of a recursive self-join ("SQL Help") tripped a memory.
Dr. Codd dealt with the BOM question very early on in his development
and exposition of the relational model, and gave the issue a meaningful
review (Ch.28) in his book: 'The Relational Model for Database
Management Version 2' (Codd, E.F. Addison-Wesley 1990).
In response to critics who held forth that the relational model could
not handle BOM-type hierarchies, Dr. Codd wrote (p453):
"A hierarchy may be an adequate representation in a few manufacturing
environments, but in many - probably most - it is not adequate. In these
latter environments, a particular type of part may be an immediate
component of several types of parts, not just one. A second,
all-too-rapid conclusion is that a DBMS is needed that exposes
network-structured data to users.
"In fact, in 1970 I presented [Codd 1970] an extremely simple
representation of product structure in the relational model by means of
the COMPONENT relation:
COMPONENT (SUB_P# SUP_P# Q1 Q3 ...Qn)
"where SUB_P# denotes subordinate part number, SUP_P# denotes superior
part number, and Q1, Q2, ..., Qn denote immediate properties of each
particular subordination.
"Incidentally, if (p1,p2,q1,q2,...,qn) is a row of the COMPONENT
relation, then part p1 is an IMMEDIATE component of part p2." [And
here's the rub:] "The fact that part p is a non-immediate component of a
part p7 (say) is not directly represented in the COMPONENT relation. A
fact of this type can be easily derived by the RECURSIVE JOIN operator
...
"In a computer-oriented sense, this kind of representation in a relation
is adequate for all kinds of networks, whether they happen to be pure
hierarchies, acyclic nets, or nets in which cycles may recur..."
"...the relational representation is probably not the best for use by
human beings, for whom graphs drawn as pictures appear to be more
comprehensible and suitable. However, that subject can be discussed
separately, and handled by separate code, when presenting data to people
in a form more consumable by people (e.g., the formatting of reports)-it
has very little relevance to mechanizing the management of data."
****************
What Dr. Codd appears to say is that ANY LEVEL of BOM subordination can
be achieved with the simple "COMPONENT" structure described above. No
extension (add'l columns) is required to recognize n LEVELS of
subordination; and self-joining allows any part to be tracked to n
depth.
And he addresses the applicability of 'nested sets' to this application.
It didn't take long last week to figure out that a) the nested set
approach is the 'visual', 'human-suitable' approach which he considers
representational, b) that the right/left adjacency proposed within the
nested set model wraps a layer of 'metadata' around base data values,
and c) where a given part appears in two or more nesting paths, the
model chokes, at best requiring that an additional layer of 'metadata'
be constructed around data values.
Interestingly, the nested set approach does seem to have value in
situations where the child-parent relationships are eternally fixed, for
example, in a condominium complex where the number of buildings and the
number of units within each building will never (barring catastrophe)
change; or where PHYSICAL adjacency needs to be described, for instance,
in a mapping application. Something to play with.
Thanks to Karen, Ben, A.G. and others who sent me down this path.
Yours in learning
Bruce Chitiea
SafeSectors, Inc.
