AnsweredAssumed Answered

One child table with two foreign keys or Two child tables with one foreign key?

Question asked by JohnColburn on Dec 19, 2014
Latest reply on Dec 19, 2014 by JohnColburn

Title

One child table with two foreign keys or Two child tables with one foreign key?

Post

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.

Thanks,

John

 

Outcomes