4 Replies Latest reply on Jan 12, 2017 3:18 PM by philmodjunk

    Contact-Company-Address hierarchy help

    ezeitgeist

      I need an additional eye as I am confusing myself on a hierarchy.

       

      Currently, I have a table of Contacts and a table of Companies. The two tables are associated by Company_id, which is the Company serial id.

       

      Now, I have found that certain companies have 2 addresses, based on having offices in 2 different regions. I already have a region field in the Company Address within the Company table.

       

      I am trying to figure out the most efficient way to add a second address and be able to associate a Contact with a given location/address.

       

      The 2 Options I have thought of are:

       

      (1) Put a Primary Address and Secondary Address within the Company table, and add a "Location" field in the Contacts table, and depending on the Location field entry (ie. "0" or "1"), the Primary Address is shown and the Secondary Address is hidden, or vice-versa in a Contacts Layout and in generated mailing labels.

       

      This option involves a little bit additional work on how things are displayed and depending on any new layouts, may make for a lot of extra work.

       

      (2) Add an Address table that is linked to Company via Company_id. This way a Company can have more than one address. I then have a location/region field in the Address table and in each Contact in the Contacts table, I ... use a Region field in the Address table to pull up the appropriate address for that contact??

       

      This is where it gets a little murky. I want to keep things as easy as possible. If a Contact changes Companies, I want to make the address change as simple as possible. Associating with just a single company is an easy change. If I have to select a Company change and then make sure I select an Address region, I feel like it is going to get complicated fast.

       

      SO ... is there a better way? Am I over-thinking things somehow?

        • 1. Re: Contact-Company-Address hierarchy help
          BiotechDave

          We have decided to tackle this problem a bit differently but I think it may help you.

           

          Many of our Contacts work out of a branch office or even from their homes so we have an address in Contacts independent from that in companies.   I do have a button that will pull the address from Companies if that's the one they plan to use.

           

          Good Luck.

           

          David

          • 2. Re: Contact-Company-Address hierarchy help
            ezeitgeist

            David -- I was thinking of having that option as well, for those working independently (an "Address override" of sorts).

             

            For those linked to companies with multiple addresses though, what if I created a Calculation field in Contacts that depending on Location field entry (I renamed to "Address_Selection") it either pulls the Primary or the Secondary address from the Company table? Would that Calculation field take up too much processing power?

            • 3. Re: Contact-Company-Address hierarchy help
              electon

              In some cases Contact is related to Address by address primary key, while Address to Company.

              They may be joined by a separate Account table so same structure and logic can be used for Customers.

               

              A company can have many addresses, but contact is usually associated with one specific address, the work place.

              When contact changes company, the data usually changes. Like phone number, email, etc.

              So it's not as easy as linking to another company.

               

              Thomas.

              • 4. Re: Contact-Company-Address hierarchy help
                philmodjunk

                You might consider a table of addresses that can be linked to both companies and contacts using different table occurrences. You could use a company ID field to link an address to a company to link more than one address to a company and can use an AddressID to link that address to multiple contacts.

                 

                This allows you to make corrections and updates to a single address record and have it show for both the company and for contacts without needing to make the same update more than once.