locked
How to approach nested tables referring to a collection of items RRS feed

  • Question

  • Hi Guys,

    I need a bit of help with an application that I'm developing.
    Here's what I want to do:

    I have a material code that I want to input as reference to spool out a list of items. Now these items could be assemblies and/or sub-assemblies and/or parts. Each assembly and/or part can be made up of some other assemblies and/or sub-assemblies and/or parts.
    This nesting could be up to 5 levels deep, depending on the material.

    My idea was to create a table with all of the parts then work my way up the chain to the material. However sometime the material could refer to only one part or even a part with an assembly that contains that part.
    I'm a bit confused as to how I would relate the tables in my database to achieve this.

    Maybe someone can shed some idea as to how I would accomplish this?

    Friday, July 22, 2011 6:41 PM

Answers

  • Reinhard,

    Sounds like you need to set up a classic Bills of Material schema where each item in the table (BOM) lists "internal" part numbers. Thus you'll need an internal part number table that either maps directly to the bought in part or to an "internal" assembly. The assembly BOM can have both discrete parts or additional sub-assemblies. On so it goes on until you have a complete list of materials needed to build the final package. A is made of B + C + d. B is made up from e + f + G + d. C is made up from h + i + j + d. G is made up from x + y + d. I've used the Upper Case to designate an "internal" Assembly and lower case to designate a discrete component. With this convention discrete really means bought in item (Say in Electronics Manufacturing - Resistor, cable assembly or Circuit board..........)

    If you want a schema diagram fragment I'll see what I can do next week. Please bear in mind that I'll be focussed on RTM tests and evaluation!

    Hope I've given you a hint in the right direction.

    Thanks, Keith

     BTW - I call the complete list of discrete items an exploded BOM and the first tier an un-exploded BOM ! :-) (I know, it must be the weekend)


    Friday, July 22, 2011 10:18 PM

All replies

  • Reinhard,

    Sounds like you need to set up a classic Bills of Material schema where each item in the table (BOM) lists "internal" part numbers. Thus you'll need an internal part number table that either maps directly to the bought in part or to an "internal" assembly. The assembly BOM can have both discrete parts or additional sub-assemblies. On so it goes on until you have a complete list of materials needed to build the final package. A is made of B + C + d. B is made up from e + f + G + d. C is made up from h + i + j + d. G is made up from x + y + d. I've used the Upper Case to designate an "internal" Assembly and lower case to designate a discrete component. With this convention discrete really means bought in item (Say in Electronics Manufacturing - Resistor, cable assembly or Circuit board..........)

    If you want a schema diagram fragment I'll see what I can do next week. Please bear in mind that I'll be focussed on RTM tests and evaluation!

    Hope I've given you a hint in the right direction.

    Thanks, Keith

     BTW - I call the complete list of discrete items an exploded BOM and the first tier an un-exploded BOM ! :-) (I know, it must be the weekend)


    Friday, July 22, 2011 10:18 PM
  • Hi Keith,

    Well thank you for taking the time to evaluate my request.

    That is exactly what I had in mind, the lowest end of my tables would be the BOM listing each part with their details.
    The second table would be listing the assemblies and/or sub-assemblies.

    Now I don't quite know how I would include a sub-assembly in an assembly comming from the same table. Unless I approach this differently!
    This application is targeted for the sheet metal industry, but it would be applicable in many other catagories.
    Material Code>>Assembly1>>Assembly2>>Assembly3>>Assembly4>>Parts
                           &/or Parts     &/or Parts     &/or Parts      &/or Parts

    Thanks for you're offer in designing a schema, however I feel that I don't want you to do the work. But you welcome to show me what you have in mind.

    Saturday, July 23, 2011 8:24 AM
  • Reinhard,

    How about something based around the following tables

    ACME_SM_Part

    Id, PartNumber[string], , Description[string], IsAssembly[bool]

    ACME_SM_BoM

    Id, AssemblyPartNumber[ACME_SM_Part], Quantity, PartNumber[ACME_SM_Part]

    [ACME_SM_Part] is a one to many relationship - Yes there are two relationships to the same table! 

    In this case, everything you make is an assembly, including the final product. Each BoM describes the materials used including both discrete parts and assemblies. All the assemblies must have BoMs. All items must have a part number.

    You could then consider creating a ACME_SM_Expanded_Bom table as a temporary holding area for you to add your discrete part quantities. You would then need to add, say a button, where  the "Execute code" calls an ExpandBoM function, where you pass the PartNumber and the Quantity, that looks for all the AssemblyPartNumber entries in the ACME_SM_BoM table that have this PartNumber and adds the resultant/respective Quantities and PartNumbers of each entry to the temporary table. IF the PartNumber's (within the BoM table) IsAssembly == TRUE then, instead, you pass this PartNumber and the rolling Quantity to ExpandBoM function within the ExpandBom function (Called recursion if you haven't come across this before) and eventually, after traversing in and out of every assembly/sub-assembly, you should end up with ACME_SM_Expanded_Bom containing all the discrete component quantities of this assembly and its sub assemblies. Your next challenge is to aggregate these quantities so that all, say, the 10mm washers are summed up etc so that you have a nice concise parts list.

    I use the word "challenge" as SQL aggregate functions aren't "out of the box" with LightSwitch yet!

    Keith


    Just to clarify the ACME_SM_BoM table with two assemblies could look something like:

    Id, AssemblyPartNumber[ACME_SM_Part], Quantity, PartNumber[ACME_SM_Part]

    1,44-550-7665, 3, 55-550-6783

    2,44-550-7665, 1, 54-662-7855

    3,44-550-7666, 4, 55-550-6783

    4,44-550-7666, 2, 54-662-7855











    Saturday, July 23, 2011 1:35 PM
  • Hi Keith,

    Thanks for the advice, it might take me a while to process this. I'm only starting off with databases & lightswitch and my knowledge on VB is not that good. It's gonna take me some time to get a working app. I'll keep posted if I have something.

    Tuesday, July 26, 2011 4:03 PM