Brad: After beating my head against a level-oriented manufacturing schema, I could have written your BOM testimony. Same issues.
If recursive joins are what I think they are, we might be able to consign 'hierarchical levels' to the representational sphere, as Dr. Codd suggests; and deal with the complexities of: raw material to part to assembly [subn] to assembly [subn-1] ... assembly [master] in a much abbreviated fashion. I had gotten to this construction, where I discriminated between parts and assemblies: PART <-->> P2A <<--> ASSEM and further: T1.ASSEM <-->> A2A <<--> T2.ASSEM and further: A2A <-->> A2P <<--> PRODUCT Look familiar? Maybe 'parts' and 'assemblies' are really the same animals, logically. Appreciate your interest also, very much. Bruce > -------- Original Message -------- > Subject: [RBASE-L] - Re: Exploding BOMs, Revisited > From: "Brad Davidson" <[email protected]> > Date: Thu, March 22, 2012 3:54 pm > To: [email protected] (RBASE-L Mailing List) > > > I'll pipe in on this, am very interested in this discussion and had the same > questions as Karen. > > > > We're an FAA repair station and BOM coding has been a constant evolution > with us over the years: an end item component can have several assemblies, > and each assembly can have several sub assemblies, with each hierarchal > level having their own piece parts, and potential usage of all across > several disparate end item units within the same OEM family, and in some > cases, across OEM's! I've had to code for set (finite) level of hierarchy, > but, it's possible additional level(s) will be introduced in future > manufacture designs. > > > > Thanks to all for sharing in this topic. > > > > Brad Davidson > > Aero-Craft Hydraulics, Inc. > > > > > > > > From: [email protected] [mailto:[email protected]] On Behalf Of > [email protected] > Sent: Thursday, March 22, 2012 2:10 PM > To: RBASE-L Mailing List > Subject: [RBASE-L] - Re: Exploding BOMs, Revisited > > > > 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.
