2 Replies Latest reply on Dec 19, 2014 2:09 PM by JohnColburn

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

    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