6 Replies Latest reply on May 4, 2013 2:40 AM by LyndsayHowarth

    Please help me avoid a "Many to Many" Relationship




      Background: I am working on compiling a parts database. The parts table is related by field "Part Number (PN)" to all features, drawings, aliases, and each assembly, so each table is indexed by PN. I also made a Table for Orders, here is the problem. An order can consist of parts, assemblies, or both. We often make new custom parts that get a PN on a new order, but not every time. I have tried to relate the Parts Table to the Order Table by Order Number (ON) and include that data per part (e.g. part 0123 has data "Order#: 3, Quantity: 6"). I can enter an Order part by part, but some orders have dozens of parts per assembly.


      Ideal Operation: Get order> go to Order layout> Select: #1 Parts/ #2 Assemblies / #3 Both->#1 (Parts) Enter Order part by part and auto update Part table with order number each was used in / #2(Assemblies) Select from value list of assemblies that have been done before and then have a multiplier field if more than one of an assembly is needed or different assemblies are needed per order. Then auto update each part in Parts table to show order used. / # 3(Both) do #2(Assemblies) and then #1(Parts) for individual parts.

      NOTE: I don't expect a solution for the ideal process, but I thought it may help for offered solutions.


      The problem: I haven't found a fix for adding a full assembly (#2) with the part breakdown on the Order table. I want to avoid a "Many to Many" relationship because that would put me in a back-and-forth motion between finding what parts are needed for the order and adding orders/ assembly numbers to each part and order for anything that wasn't a standard set.


      Thank you in advance,



        • 1. Re: Please help me avoid a "Many to Many" Relationship

          1) You shouldn't be "adding" Parts to an Order, you should be adding Order Items to an Order. An Order Item is a record in new table that we call "join" tables. It has a field for the Order primary key and the Part primary key.


          2) How you handle Parts and Assemblies can go two ways, I think.


          You simply treat the two tables as the same. The primary key of the "part" in the Join table could be the primary key of the Assembly. It will take some more overhead if you've got auto-enter calculations in the Join table.


          Or you could make ALL "Parts" part of an Assembly, even if the Assembly consists of 1 quantity of 1 Part.


          3) You should script your current inventory counts. You could do it with calculations (Parts::Count - Parts::Used since last count), but that's going to be unstored calculation and slow to find on and to view in a list.


          4) You should stop using your human-readable Order Numbers, Part Numbers, etc for relationships. Use an auto-enter calculation of Get ( UUID )

          1 of 1 people found this helpful
          • 2. Re: Please help me avoid a "Many to Many" Relationship

            David is right... your orders have items.... some parts have items. This is an issue we deal with all the time when dealing with catalogs with associated other catalog items. Some sold with a 'bundle' or consolidated price and others with separate line-ltem prices


            In order to group these items with associated other parts you have to further classify them by their group... let's call it "Team".

            What you have to identify is whether the Team is one of the Members or just the leader, and whether those parts will ever be members of or leaders of another Team. This is what is going to determine whether you need that many to many join relationship or not.


            I have a client who sells robots and parts and various assemblies. Several models share the same parts. Knowing this was and is now and for the foreseeable future will be limited to a handful of models - so I used a repeating field with part valuelist to record the Team that part was a member of and another field in the model record to record the Team leader name. You then create a self relationship from the Team leader name to the Team member field.


            So... now you have 2 kinds of parts... some which have a value in Team leader, and others which don't... but do have matching Team member values so can be isolated as a set..


            ... I will try to get back to finish this ....


            - Lyndsay

            1 of 1 people found this helpful
            • 3. Re: Please help me avoid a "Many to Many" Relationship


                   I like the idea of Get(UUID).  I was planning on doing the inventory counts once I found a fix for this "order issue". When I said adding parts to the order, I meant inputing the keys to call the part details made in the parts table into the order table. I think the first option of parts table would work better, since some individual parts span 30 or more assembly options


                   Would the join table work if parts weren't mutually exlusive to each assembly or order, or would the "Join" table have basically a duplicate record for each order a part was used on?  


                   Correct me if I'm wrong, the UUID would be able to cross any number of relationships, so it wouldn't matter how "weblike" my relationships are, it would be Universal. If that is true, then that may solve some of my other issues regarding Parts to Assembly and Both to the order.


              I will try some of these options and update the thread on my results.



              Thank you,



              • 4. Re: Please help me avoid a "Many to Many" Relationship



                     Currently I have fields that are only instanced once, but have scripts that add to the field (e.g. Field:Part used with assembly may have "(2)  per assembly option #7" and then I add in the assembly #8 so the field reads "(2) per assembly option #7, {carriage return} (1) per assembly option #8"). My knowledge of repeating fields is near zero, so I didn't want to mess up the database.


                Thank you,



                • 5. Re: Please help me avoid a "Many to Many" Relationship

                  I figured using UUID was the first step before making the join tables...

                  My train of reason was an auto enter text ID on the Parts table and then all auxillary tables simply have their key copied from the Parts table key, so each feature record or alias record had the same ID as the Part record.


                  So I tried the UUID and all of the scripts that look for related records broke beyond my current ability to repair after 2 hrs, and I had to reload an older version before UUID. I guess my level of understanding of the UUID function needs some work.


                  I've decided to work on the join tables for now on the older save, and figure out the UUID along the way.

                  • 6. Re: Please help me avoid a "Many to Many" Relationship

                    That's a pity if you are being closed minded about the repeating field. It is just simply a field which has been told to have more rows in it.


                    There is also an important conceptual lesson about relationships and lists.


                    My suggestion was in response to your resistance to putting in a join table.

                    It does not contradict anything which David has said and in fact builds upon it. It was quite simply a way to deal with the issue of parts belonging to several assemblies as well as an assembly having many parts.... which I do not believe you have yet got you 'head around'.


                    - Lyndsay