6 Replies Latest reply on Jul 28, 2009 7:21 AM by TSGal

    stuck on proper table relationship

    timothy2k

      Title

      stuck on proper table relationship

      Post

      I am getting closer (I believe) to finishing all my table and relationships setup, but I am stuck on how to get rid of a many to many relationship.  Any general advice would be very helpful and apreciated!

       

      I hope this explanation helps:

       

      An estimate is unique by a customer id and an estimate id. Estimates, however, can have duplicate parts.

       

      So, the

      estimate id's can look like: E10020, E10020-1, E10020-2, E10021, E10022 etc.

      customer ID's look like: EVPT, MTYF, SDRT (basically 4 letter codes)

       

      Quotes are then created and sent out to vendors to quote (or "bid") on.

       

      I've been trying to group the quote items by estimate id & customer id, which I hope would allow me to have one quote display all the estimate parts associated with that vendor in one simple quote.

       

      The tricky part is that one or all parts of an estimate can be sent to one or many different vendors. I am trying to figure out how to group all or one part of an estimate into one quote for one vendor, for each vendor I want to send a quote to.

       

      I have the following tables:

      customer

      estimate

      quote item

      quote

      vendor

       

      The problem is, when I try to relate records in quote item to one quote- it becomes a many to many relationship.  I attached a picture of the relationship setup, and would be glad to provide further explanation to anyone who may be able to help.

       

      http://img505.imageshack.us/img505/9872/relationships.gif 

       

      *Just ignore the purchase order and invoice part of things for now, I will use the same approach for those once I figure out this quote part.

       

      Any advice is very much apreciated.

      -Tim

       

       

       

       

        • 1. Re: stuck on proper table relationship
          etripoli
             I don't usually worry too much about many-to-many relationships, as the only way to avoid them is the specify 'Unique Value' in the validation of the field.
          • 2. Re: stuck on proper table relationship
            timothy2k
              

            Hmmm.....I thought it was a pretty steadfast rule that many-to-many's should always be avoided by using a join table and creating 2 one-to-many's?

             

             

            • 3. Re: stuck on proper table relationship
              etripoli
                

              In Filemaker or Access?  I don't avoid many-to-many in FM, but I use unique validation whenever possible.  If you have more than one field on each side of the relationship, a one-to-many can't be acheived*.

               

              * if it can, your data isn't normalized!

              • 4. Re: stuck on proper table relationship
                timothy2k
                  

                I'm not really sure what you are saying.  Even if I left the many-to-many, if you look at my scenario it does not solve my issue- I need to combine different variations of estimate parts into different quotes, quotes that can then be sent to many different vendors.

                 

                 

                 

                 

                 

                • 5. Re: stuck on proper table relationship
                  etripoli
                    

                  Let me see if I understand the process - is it like this:

                   

                  Create/select a customer

                  Create/select an estimate

                  Add items to estimate

                  Assign items to a quote

                  Send quote to multiple vendors

                   

                  Or:

                   

                   

                  Create/select a customer

                  Create/select an estimate

                  Add items to estimate

                  Assign items to one or more quotes

                  Send quotes to multiple vendors

                   

                  Basically, is one item, on one estimate, only on one quote, or can it appear on many?  If many, then your structure should be

                   

                  Customers

                  Estimates

                  Estimate Items

                  Quotes

                  Quotes Items

                  Vendors

                  • 6. Re: stuck on proper table relationship
                    TSGal

                    timothy2k:

                     

                    Thank you for your post.

                     

                    Unfortunately, I am unable to see your relationships.  I assume the site is either down, the link is bad, or the graphic has been removed.

                     

                    I'm not sure why you want to get rid of a many-to-many relationship, as it is used for many solutions/applications.

                     

                    "An estimate is unique by a customer id and an estimate id.  Estimates, however, can have duplicate parts."

                     

                    If an estimate is unique, it should have a unique estimate id.  Would there always be one customer id for an estimate?  If so, then that would be a one-to-one relationship.  If an estimate can have duplicate parts, then the relationship between the estimate and duplicate parts is one to many, where the primary key field would be the estimate id.  However, you only listed five tables, and I'm not sure which table contains the parts and/or part id.

                     

                    "Quotes are then created and sent out to vendors to quote on".

                     

                    Quotes sounds like a join table, since you are sending out to vendors and it contains many quote items.  True?  I assume this is also based upon an estimate, so that's where I'm a bit lost.  I'm not sure what is the quote item.  Why would only some part of an estimate be sent to a vendor?  Maybe you want to break this down further.

                     

                    Perhaps you can provide a specific example.  This will provide more information, and maybe by describing it, you'll realize what you need to do!

                     

                    TSGal

                    FileMaker, Inc.