1 Reply Latest reply on Oct 22, 2013 8:31 AM by philmodjunk

    A relational question



      A relational question


           The main table of my database is Projects, this is related as a child record to Companies which in turn has Representatives_of_Companies as a child table. Within the Project table there are fields that are to be populated via the Companies table, namely Client and Associate_Company (ten fields from 1 to 10).


           At the moment the Project table has an fk_client_serial for the Client field but the Associate_Company (1-10) only use a list derived from Companies with explicit relation and pk/fk interchange. 



           So, my query, is simple. Is this good practise the way I am going about this or is it just an inept way to go about it. 



           Should I try to create a new TO for Companies and try to relate Associate_Companies?

           Should I just fill Associate Companies with a serial no. (pk) of Companies from the parent table? 


        • 1. Re: A relational question


               You need an additional table or two to manage what are actually many to many relationships. As I understand your description, any given Project record can be linked up to 10 Clients and to up to 10 companies. This indicates that any Project can be linked to many clients while any client can be linked to any project. The same can be said of Projects to Companies. I'll just focus on Clients to Projects, but the same needs to be done for Project to Companies as well:

               Start with these relationships:


               Projects::__pkProjectIDID = Project_Company::_fkProjectIDID
               Companies::__pkCompanyIDID = Project_Company::_fkCompanyIDID

               You can place a portal to Project_Company on the Projects layout to list and select a Companies record for each given Projects record. Fields from Companies can be included in the Portal to show additional info about each selected Companies record and the _fkCompanyIDID field can be set up with a value list for selecting Companies records by their ID field.

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained