One child table with two foreign keys or Two child tables with one foreign key?
I have tables: Companies & Contacts. Both Companies and Contacts would possibly have multiple addresses so I created a third table: Addresses.
The Addresses table contains two foreign keys, fk_Companies_ID and fk_Contacts_ID. Only one of these foreign keys would be populated for each record.
On a Companies layout, There are two portals:
Contacts Portal: relationship is Companies::ID = Contacts::fk_Companies_ID
Addresses Portal: relationship is Companies::ID = Addresses::fk_Companies_ID
On the Contacts layout, there is one portal:
Addresses Portal: relationship is Contacts::ID X Addresses::ID
The portal is then filtered with the following:
Addresses::fk_Companies_ID = Contacts::fk_Companies_ID OR Addresses::fk_Contacts_ID = Contacts::ID
This portal shows company and contact addresses for the current contact. There are rules in place so that a company address can't be edited from this portal.
My question is: Am I going about this wrong and should I just have two separate address tables, one for contacts and one for companies? I am just at the beginning of this project and it works fine, but possibly there are pitfalls that I can't see or anticipate.