Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2002

    Engineering Change for BOM

    I have created the BOM structure as below.

    Now there is the need to add the engineering change for BOM data structure. My thought is as beow.

    The data structure of engineering change is nearly the same as that for BOM. Is this structure the popular one? Or is there any other popular one and alternatives? Please advise, thanks.


  2. #2
    Join Date
    Sep 2002
    Sydney, Australia

    We really need to see the >new< functional requirement. On face value, you definitely do not need the two Change tables as suggested, you only need:
    _ Change(ID,change_action,qty)
    and Change.ID = Item.ID

    IMHO, having BOM-database experience, I would suggest the following naming as it would improve understanding:
    _ Item(ItemId, Description, Color, Weight,...)
    _ ItemAssembly(AssemblyItemId, ComponentItemId, Qty)
    _ ItemChange(ItemId, ChangeAction, Qty)

    _ an ItemId is made up from its [ItemAssembly.AssemblyItemId] ComponentIds
    _ an ItemId is used in its [ItemAssembly.ComponentId] AssemblyIds

    1 The ID key is weak (identitity columns are bad news, lead to future limitations and encourage poor design), you need a real PartNo. You also need a Version or ReleaseNo or DesignDate (not a ManufactureDate. The Item PK will then be PartNo plus ReleaseNo.
    2 Basically you need a method of identifying manufactured batches separate from design releases within the BoM list. The inventory of parts and the structure of parts are separate tables sharing one BoM list.
    3 The engineering change will then clearly be applied to Item (design oriented), or to the manufactured Batch (manufacture oriented). This will eliminate (or at least clarify) Change.Qty. Insertion into ItemChange would probably cause a new Item (same PartNo, increment ReleaseNo) as well.

    _ Item(PartNo, ReleaseNo, Description, Color, Weight,...)
    _ _ index PK (PartNo,ReleaseNo) unique
    _ ItemAssembly(AssemblyPartNo, AssemblyReleaseNo, ComponentPartNo, ComponentReleaseNo, Qty)
    _ _ index Assembly (AssemblyPartNo,AssemblyReleaseNo) unique
    _ _ index Component (ComponentPartNo, ComponentReleaseNo) unique
    _ _ (choose whichever index has greater spread for the PK)
    _ ItemBatch(PartNo, ReleaseNo, ManufactureQty, AvaliableQty)
    _ _ index PK (PartNo,ReleaseNo) unique
    _ ItemChange(PartNo, ReleaseNo, ChangeAction)
    _ _ index PK (PartNo,ReleaseNo) unique
    _ _ ItemChange is a child of either Item xor ItemBatch

    Last edited by DerekA; 12-11-02 at 23:13.
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts