1 Reply Latest reply on Aug 17, 2015 1:37 PM by erolst

    How do I fix my relationship issues with three tables?

    ggoresky

      I have three tables to link, and I am experiencing confusion about how to identify and link Primary and Foreign keys.I have a team of owner and dog who visit facilities for Pet Therapy. I have managed to create a linked visit table, and added the spreadsheet data to show the hours by each team member, but I cannot sort out how to link the facility to the visits.

      • Contact summary has a TeamID_PK field. It currently does not have a facility field, but I would like to identify, in the future, each facility visited by each team.
      • Visits, with VisitID_PK is linked to the Contact summary with a TeamID_FK field, and shows hours. I want this table to have a facility field linked to the next table, a Facility table, with a list of all facility information. I would like the Facility field in this table to be a dropdown list of the facilities for data entry and editing. Historically, many of the facility fields in the table will remain empty, as the information was not originally collected using facility name.
      • Facility table with FacilityID_PK lists all of my facilities with contact information. Facilities can be visited by multiple teams, and each team can visit multiple facilities. For each visit, there can be only one team per visit.

      I have set up a contact detail Layout with tabs. One tab includes a portal for the visit information. It seems that a portal can have information from one table only. The hours show up, but I cannot put in a facility.  I do not know how to get out of this box. It would seem that this is an issue with the relationship of tables.

      I am a new user, so my familiarity with many of the tools is limited.

        • 1. Re: How do I fix my relationship issues with three tables?
          erolst

          ggoresky wrote:

           

          I have three tables to link, and I am experiencing confusion about how to identify and link Primary and Foreign keys.I

           

          When setting up a data model, you must identify your entities/processes and their relationships/cardinality; a common way is to write a prose description of what are essentially your business rules, then find/extract the pertinent nouns and verbs from that text (essentially, your business rules):

           

          "Teams attend Visits in Facilities"; "Facilities can be visited by multiple teams, and each team can visit multiple facilities"; "a team is formed by a person and a dog"

           

          which could be translated into

           

          Facility --< Visit >-- Team --< Contact (Dog/Person )

           

          Not really sure about the Team part, though; if any person/dog could be member in many (well, more than one) teams, you may want to consider

           

          Facility --< Visit >-- Team --< TeamPartner >-- Contact (Dog/Person )

           

          which is more flexible, and should even be considered if contacts will be limited to one team for the lifetime of your database (or their own) …

           

          Next step is finding the correct attributes for your existing entities – which sometimes requires their re-evaluation – but this is an iterative process, and you need to start somewhere …

           

          ggoresky wrote:

          It seems that a portal can have information from one table only.

           

          No, in fact you can put fields from any table occurrence that is “behind” a portal's table occurrence into that portal (with certain caveats). So in the data model outlined above, a portal into Visit would show all related Visit records and allow you to combine fields from Visit and Team.


          Be aware, though, that a portal can only show records from one table. So while you could even “look into” Contacts, a Facility has more related Contact than Visit records, so there it would get tricky …


          Once you got that right, you can think about the workflow – e.g. use a value list of Teams to add Visits into a portal in Facilities, or a value list of Facilities to add a visit into a portal in Team? (There are more elegant ways of adding related records, but that method is a good start.)

           

          If you do add data via a portal, don't forget to check the relationship setting “Allow creation of related record in this table …”.).