10 Replies Latest reply on Feb 8, 2016 5:28 AM by DanielShanahan

    Many / Many relationships

    rob54875

      I've been beating my head trying to figure this out. I have a widget store. I get orders for many types of widgets. Some of these widgets are made up of still smaller widgets (making sets), which I also sell. What I'm trying to do with limited success is adjust inventory. The widgets that have no sets they are part of are fine. Order comes in, deduct the qty from inventory straight across, no problem. But I can't seem to get my head around how to deduct from the individual smaller widgets when an order for a widget set comes in. To make things worse, there are several types of sets which also contain the same widgets. What I have is:

       

      Products table, full of widgets and sets

      Order Details table, same

      An Affected products table, containing the various widget sets, widgets which make these up, and the quantity of each individual widgets needed to make the various set.

       

      This is where I'm stuck. I'm not sure how to parse or link the tables which will allow me to change the quantities of the associated widgets. Any help, or a different way of looking at this, would help a lot. My eyes hurt and I've developed just a bit of a headache. Thanks!

        • 1. Re: Many / Many relationships
          Vaughan

          Are the "sets" treated as inventory items themselves? I mean, does somebody get the individual items and make them up into sets and put them onto a shelf? Or when somebody orders a set, are the items gathered from the individual items?

           

          If Item A is in Set A, would the total inventory count for Item A be (Item A + Set A)?

          • 3. Re: Many / Many relationships
            rob54875

            Thanks for checking this out. Actually, no, the sets are not inventoried items, just the individual products. Although I tried this, I can't grasp how to change the inventory on all the included items, which are part of other sets as well. I'm working toward calculating an inventory number for the sets based on the individual widgets they contain, but that won't be a problem. Also, the total inventory count of your example would just be count of A, not including sets which contain it.

             

            For my real world example, I sell coins. Some are sold individually, some in rolls, and some in sets (ie. 5 of each in one roll, still a set). If I get an order for a set, I need to be able to deduct 1 or more of each individual coin type that may be included in the set. Keeping in mind the individual coins themselves are part of several different sets.

             

            I tell ya, it's like writers block. I should know this. Thanks again,

            • 4. Re: Many / Many relationships
              Vaughan

              rob54875 wrote:

               

              I tell ya, it's like writers block. I should know this. Thanks again,

               

              This is actually really hard.

               

              As Tom's post indicates, it can get into recursive data structures.

              • 5. Re: Many / Many relationships
                rob54875

                I was just reading that article, and thanks Tom for that, and yea, a bit heady. Although it might add a new dimension which could help, that's a project for another day. I'll see if I can make it a little simpler here.  Thanks guys!

                • 6. Re: Many / Many relationships
                  DanielShanahan

                  Rob, you are describing a manufacturing scenario.  To accommodate that you would create a Bill of Materials (BOM) table.  The BOM is a child table of the ITEM table (sometimes called PRODUCT table).   For example, you may have the following records in your ITEM table:

                   

                  ID|item|cost|price

                  1001|Indian Head Coin|5.00|25.00

                  1002|Indian Head Coin Roll|50.00|250.00

                   

                  The BOM portal for the first item would be

                   

                  ID|itemID|qty|unitOfMeasure

                  1232|1001|1|EA

                   

                  The BOM portal for the second item  - the roll - would be

                  ID|itemID|qty|unitOfMeasure

                  1232|1001|10|EA

                  • 7. Re: Many / Many relationships
                    rob54875

                    Thanks Daniel! I appreciate the effort. I've sort of done this in reverse already, with the roll being the lead. (So the coin would be .10 instead of 1.0) Any idea how to deal with sets though? Say I have a roll of half D Mint and half P Mint. Or a set of 15 different coins. I'll check for info on BOM tables too. Thanks again,

                    • 8. Re: Many / Many relationships
                      DanielShanahan

                      rob54875 wrote:

                       

                      Thanks Daniel! I appreciate the effort. I've sort of done this in reverse already, with the roll being the lead. (So the coin would be .10 instead of 1.0) Any idea how to deal with sets though? Say I have a roll of half D Mint and half P Mint. Or a set of 15 different coins. I'll check for info on BOM tables too. Thanks again,

                      I recommend having a single coin count as one unit instead of a percentage of a larger unit.  The advantage is that you would then be able to sell a single coin, which you can't do if you count it as .1 unit.

                       

                      Attached are a couple of examples.  The first is an item with a BOM.  However, if your items are serialized, then take a look at the second example.

                       

                      In the examples I mention a TRANSACTION table.  I have a file on my website that illustrates various methods for updating quantities and using transactions.  I also have a YouTube video, although the final method - keeping the quantities in a separate table is only in the download file and not in the video.

                      • 9. Re: Many / Many relationships
                        rob54875

                        Thank you so much Daniel! I haven't disappeared, just going over this. It's a different (and better) way of doing what I was thinking about but couldn't do. I've got a lot of work to do, lol.

                         

                        Rob

                        • 10. Re: Many / Many relationships
                          DanielShanahan

                          I'm glad the file is helpful, Rob.

                           

                          Good luck!