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?