Didn't even wait for a bathroom break to read this! My comments: I don't understand what his Q1-Qn column are for in his table. I'm hoping he doesn't mean that you would need to create a finite amount of columns for levels....
I'm interested in your quote: "and self-joining allows any part to be tracked to n depth." Is that just a thought of yours, or did Codd have an example of how a self-join would achieve that? Karen In a message dated 3/22/2012 1:23:41 PM Central Daylight Time, [email protected] writes: > 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. > > >
