G'day all I work, on the operating side, of a Firebird database where the developers have treated all assemblies, sub-assemblies, raw materials and parts are classified as individual items in an ItemMaster table. BOMs are handled in BOMMaster and BOMLines tables - the item being assembled is in the BOMMaster and the components, including any sub-assemblies are in BOMLines. There is an option to set sub-assemblies as "Phantoms" that do not show as the sub-assembly in the AssemblyLines table but show as the individual components with any associated instructions. The BOM tables are the recipe while the AssemblyMaster and AssemblyLines are the actual assemblies that are initially populated by the BOMs and can be modified by the user to reflect the actual usage of items, labour, etc.
Regards Clive Williams -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Bruce Chitiea Sent: Friday, 23 March 2012 10:20 To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Exploding BOMs, Revisited 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.
