Part Database Version, Equivilence, Superceedence,Obsolescence in Assembly Bill-of-Materials

This page describes functions that are required by a part inventory or system used to construct bills-of-materials for any manufactured item. The author has experience in the electronic equipment sector, with mechanical assemblies, printed-circuit-board (PCB) design and manufacturing, but the same functions described below apply to most manufacturing businesses.

Any part management system would need to be adaptable within an inital fixed structure dictated by normal datbase constraints. This could include having versions of parts, parts being assemblies of other parts, items being grouped according to type, function, use, obsolescence, superceedence or equivilence.

While all functions may not be used by one user, a method of updating or adding in functionality shouild be automated. For example, one user of the system may insist there are only one version of any part, all replacement parts handled as equivilences. Another user may find versions of parts, controlled by start-end dates, allow for ordering to take into account of when items are avalible in time.

In addition to adding parts and Assembly lists, various methods of interrogating the part database to detect re-ordering, order expiary, obsolescence and stock control can be inplemented using the mature, formal query languages, like SQL (Structured Query Language).. Queries that are frequently used, tried and tested, should then be added to user menus so that they can be used by all staff without their needing to understand SQL.

A bill-of-materials (BOM) contains a list with quantities and other manufacturing details of each indvidual part at the level of the specified assembly. A used-on list can do the reverse: describe which assemblies or parts, and at what level, use a specified part or list of parts.

The BOM list can contain sub-assembly parts,(which contain their own list and quantity of parts). Sub-assembly parts can call other sub-assembly parts but a part cannot call itself: (which would cause an infinite recusrsion!). Form and database entry constraints should prevent these kinds of errors..

Parts may be versioned, have open or defined start-end dates and superceedence rules. A rule should be set for each version of a part. Normally the rules would be different for each part/version, makin the distinct definition why there is a separate version. Versions could be used for slightly different assemblies. A specific set of numbers having specific meanings for each number, For example, all -1 versions or all -2 versions are made by a specific manufaturer to a functionaly interchangable specification, or all -3 versions that are advance billed, would trigger a SQL query to look for all part/asseblies that list that part/version , or all -4 versions are functionally interchangeable in a mechanical sense, but have a different temperature specification. , And so on, the possibilites are endless. A rule of functional interchangability among all same part-numbers, regarless of version should be attempted. Any variant (set) of a part that is also an assembly would most likely be a modifying or add-on kit to convert a lower-level assembly or even top-level assembly for a specific market, for example: voltage, color, standard compliance.

Thus the planning of sub-assemblies needs to be worked out in the inital product planning to prevent near-duplicate custom versions of assemblies having to be stocked.

Obsolescence can be indicated by a part having an order end-date and superceeding part indicated. A rule that a superceeded part would have both an end-date and a superceedence part-number would need to be an entity and data-entry/update constraint. Entity Flags and dates should be avoided, In this case, the presence of a superceedence part-number should suffice to flag a part as obsolete.(Ensuring no obsolete part did not have a superceeded part).

Listings of part/versions should select the LATEST part as default, in any selection form, with an optional list of versions , together with the reasons for each version.

The main functions would be to create part/versions, create bills-of-materials and to query and update both types of lists. Parts can be listed with the following types of attributes. These vary between uses, but the following list are used in some the functionality described further on . Aan example list).

  • PART NUMBER: (this can have prefixes and formats as per a defined set).
  • VERSION: (normally a number).
  • TYPE (Electrical,Mechanical,etc ).
  • CATEGORY: (unspecified, could be: casing, connector, bezel, semiconductor, passive component).
  • ASSEMBLY: (Y/N) -not allways required, as more-than-one sub-part listed indicates the same criteria.
  • MANUFACTURED: (With full references to manufacturing drawing location, PDF files etc),
  • SPECIFIED:(With specification reference location pointers)
  • PURCHASED: (With a purchase specification, giving mechanical and/or electrical parameters that are critical for that specification)
  • HAVE EQUIVILENCE: (A part that can be interchanged with another part without modification, would have an equivilence listing)
  • SUPPLIER PART INFORMATION: (Supplie part-number(s), ordering information etc).
  • STORE LOCATION (The company physical location of the item).

A Company would normally have defined specifications and standards to comply with national, industrial and environemtal restrictions.

  • PROCESS SPECIFICATIONS from customers.
  • GENRIC ORDER SPECIFICATIONS (Standards and environmental comnpliance speficications).

In this case, these documents would also have references attached to all parts within the part database.

A part-management system with the above features would include intuitive menues that offered finite attributes, linked entites, provided functions to add, delete, list and detect inconsistancies in an entity.

An entity, in database parlance is a collection of data that is complete within itself. This may be a collection of table rows from a number of different tables, binary objects, images, pointers to files, files and pointers.

There are a number of existing packages that are well developed to provide some, or nearly all of the above functions, but often there are adaptions away from an ideal data-model or extensive (and expensive) programming that is essential to approximate a prefered system.