5 Replies Latest reply on Jul 26, 2011 2:08 PM by philmodjunk

    Join table? Duplicate table? Not sure here ...

    vcdpp

      Title

      Join table? Duplicate table? Not sure here ...

      Post

      Hi there.  I put brands together for joint marketing events (think pairing President Brie with Breton crackers for a shared coupon). I have a table of hundreds of companies who are sometimes the client, sometimes a prospective partner.

      On my projects detail (shown below) i have tabs where I have detail from other tables that relate to that particular project. It has worked great with all the tasks.  Now I'm trying to list all the companies that are potential partners for a particular project, and identify their status.  I tried using the foreign key Company ID again but that just repeats what I have for the client.

      Do I need to duplicate the Company Table and use that for "prospective partner"? Or would a join table (I haven't done that yet), between Companies and Projects work? Or is the answer something I haven't learned in basic and intermediate class?
      Thanks, so far I've gotten great answers on this site, you guys are awesome.

      VC

      DPP_Database_question3.png

        • 1. Re: Join table? Duplicate table? Not sure here ...
          philmodjunk

          What distinguishes a "Prospective Partner" From a "Client" can differ for each project correct?

          A join table like this should work for you:

          Projects----<Project_Company>-------Companies

          Projects::ProjectID = Project_Company::ProjectID
          Companies::CompanyID = Project_Company::CompanyID

          On a projects table, you can add a portal to Project_Company and use a drop down or pop up formatted Project_Company::CompanyID field to select companies from the companies table to add to your list of Clients and Prospective Partners. A field defined in the join table and added to the portal can record whether a given company is a "client" or a "prospective partner" if this is something that differs with each project. You can add additional fields from companies to this portal to display more information about each company you select as part of the project.

          • 2. Re: Join table? Duplicate table? Not sure here ...
            vcdpp

            Thanks!  Doing it this way, can I choose numerous companies on the tab as prospects for that project? Typically I am reaching out to from 10 to 100 companies for a given project...

            • 3. Re: Join table? Duplicate table? Not sure here ...
              philmodjunk

              That's what the portal is for. each selected company will create a new record in the join table so 100 selected companies will make for 100 join table records with the same project ID but different company Id values. They'll all be listed in this portal that I've described.

              • 4. Re: Join table? Duplicate table? Not sure here ...
                vcdpp

                I'm not good with understanding the coding around relationships. Do I put foreign keys that match the company ID and Project ID Primary keys in the new Project_Company table? I did it this way and when I inserted the Company ID field in the tab it says it's an unrelated table.

                I did create 2nd instances of the company and project tables to make the relationship graph change, in case that's it/ I can't figure out how to put another screen shot here

                • 5. Re: Join table? Duplicate table? Not sure here ...
                  philmodjunk

                  Yes you define fields for this in the Join table. Then you use the relationships tab to define the relationships need to link the three tables together in this fashion.

                  CompanyID from which table occurrence? (A "table occurrence" is one of the "boxes" found in Manage | Database Relationships.)

                  What I described is that you would use the portal tool to place a portal to Project_Company on your tab, then add the Project_Company::CompanyID field--not the company Id field from Companies to this portal row formatted as a drop down list or pop up menu of Company ID values from the Companies table.

                  Here's a demo File you can down load and examine. It matches "contracts" to "companies" in a many to many relationship. If you rename "contracts" as "Projects", you'll get the same set up that I am describing in this thread:  http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html