11 Replies Latest reply on Nov 15, 2012 6:48 AM by philmodjunk

    portal of a portal of a portal: managing nested join tables

    JamesPickett

      Title

      portal of a portal of a portal: managing nested join tables

      Post

           I designed my database to have a table for Individuals, a table for Locations they might have traveled, and then a join-table linking the two of them.  Initially, my Locations table had fields for the name of the location, the province of the location, and the country of the location.  

           Subsequently I realized that it made more sense to use another join-table to link the Locations table back on itself.  E.g., if I have a location entry for Dallas I now use a join table to add "Texas" (which has a separate entry in the table) as the location's provincial designation rather than type out "Texas" and lose the accompanying data for the province-level entry. This seemed a more robust solution because occasionally locations were part of different provinces and countries historically, so I can add multiple entries for each (e.g. list both U.S.A. and Mexico for the province of Texas and even include accompanying chronological data in the join table).

           Now the problem: Previously, I could call up anyone who had been to Texas, for instance, since that information was captured in a field on the Locations table which also showed up in the portal on my Individuals table.  Now, however, that information is captured in a join table and hence no longer shows up in the Locations portal in my Individuals layout.

           Is there a solution to this problem to get the best of both worlds?  I imagine I am not the first one to run into the problem of telescoping join tables...

           Many thanks in advance.

            

            

        • 1. Re: portal of a portal of a portal: managing nested join tables
          philmodjunk

               This one souncs vaguely familiar....

               Let's see if I have the basic relationships correct for what you have:

               My field and table names may differ from yours...

               Individuals----<Individual_Location>-------Locations-----<Join>-----LocationsByProvince

               Individuals::__pkIndividualID = Individual_Location::_fkIndividualId
               Locations::__pkLocationID = Individual_Location::_fkLocationID

               So far so good, but I'm not sure about the rest:

               Locations::__pkLocationID = Join::_fkLocationID
               Locations::ByProvince::ProvinceID = Join::ProvinceID  ----> is that what you have?

               Wouldn't it make more sense to link to a "provinces" table instead of another record in Locations?

          • 2. Re: portal of a portal of a portal: managing nested join tables
            JamesPickett

                 I think that's right; I'm not completely sure what some of your notation signifies.  "pk" and "fk"?

                 In noob terminology, an individual entry might convey that Bob visited Dallas, Houston, and Philadelphia, all of which are entries on the Location table, as are Texas and Philadelphia, the latter two being connected to the former three via a join table.  I am trying to figure out how to access the information that Dallas, for instance, is located in Texas (information contained in (Locations-----<Join>-----LocationsByProvince) from the Individuals table.

                 I considered having separate tables for "Provinces" and "Countries."   The problem with that is that the distinctions between these different geographical units are not always clear cut.  For instance, Athens could be a city, a province, and a country.  When I have an individual that comes from a village outside of Athens, for instance, it seemed more efficient to pull the provine data for that village from the Athens entry.  Does that makes sense?

                 Part of my design challenge here is that sometimes I only know that an individual is from Texas, sometimes I know that he is from Dallas, but if I pull all records of people from Texas I want him to appear in the search no matter what the specificity of my data.

                  

                  

            • 3. Re: portal of a portal of a portal: managing nested join tables
              philmodjunk

                   Don't get hung up to much on field names. I forgot to include this link: Common Forum Relationship and Field Notations Explained

                   pk is a serial number field (primary key) and fk (foreignkey) is the number field that matches to it from the child or "many" side of the relationship.

                   I'm most interested in what "match fields" you used for the location to location many to many part of your relationships.

              • 4. Re: portal of a portal of a portal: managing nested join tables
                JamesPickett

                     That link was helpful, thank you.  

                "Locations::ByProvince::ProvinceID = Join::ProvinceID" - There is no separate table for Provinces; so the join table is for two instances of the Locations table.  I.e. the fk of the first instance of the Province table matches with the fk of a second instance of the Province table.  So ProvinceID=LocationID.

                     The other match fields are right, as far as I understand.

                      

                • 5. Re: portal of a portal of a portal: managing nested join tables
                  philmodjunk

                       LocationsByProvince would be the second occurrence of Locations in my original post.

                       What I have so far is:

                       Locations::_pkLocationID = Join::_fkLocationID

                       But what match fields do you use to link the second occurrence of Locations to the join table? I'd guess that you have:

                       LocationsByProvince::_fkLocationID = Join::_fkLocationID_2

                       or some such other field in Join.

                  • 6. Re: portal of a portal of a portal: managing nested join tables
                    JamesPickett

                         Yes, that is correct.  They both use the unique id of the Locations table, but from different occurrences.

                    • 7. Re: portal of a portal of a portal: managing nested join tables
                      philmodjunk

                           It's not the different occurrences that I am checking. It's what fields in the join table are used in the link. I will assume that I am correct in my last post or now...

                           This should be quite workable and you can actually "loop" through location records from the specific to the general if you link recorsd that way and if that is of interest.

                           You could log a location for an Individual as "the Bronx", in New York city. It could then link to a location record for New York City, and that could link to a record for the state of New York .... United States..... North America......Western Hemisphere.......Planet Earth.... (Just to take it to ultimate extremes wink)

                           The challenge lies in accessing this chain of locations records as you can't place a portal inside of a portal.

                           You could use a "master-slave" pair of portals where you click a row in the Master portal, the "once removed" location records for that row appear in the "Slave" portal.

                      • 8. Re: portal of a portal of a portal: managing nested join tables
                        JamesPickett

                             How do you implement s master-slave pair of portals?

                        • 9. Re: portal of a portal of a portal: managing nested join tables
                          philmodjunk

                               Just love it when the Forum Software "eats" a long post and I have to enter it all over again. angry

                               Individuals----<Individual_Location>-------Locations-----<Join>-----LocationsByProvince

                               We'll start with a layout based on Individuals. The Master portal will be to Individual_Location.

                               Add more occurrences for this relationship:

                               Individuals----<JoinSlave>----LocationsSlave

                               Individuals::SelectedLocation = JoinSlave::_fkLocationID
                               LocationsSlave::__pkLocationId = JoinSlave::_fkLocationID_2

                               Make your Slave Portal a portal to JoinSlave.

                               Put a button in the row of the Master portal or format the entire row of fields as a button so that clicking it performs this script:

                               Set Field [Individuals::SelectedLocatation ; Individual_Location::_fkLocationID ]

                          • 10. Re: portal of a portal of a portal: managing nested join tables
                            JamesPickett

                                  

                                 I am still struggling a bit to follow the notation and logic; a few questions:
                                  
                                 How is <JoinSlave> different from <Join> in the notation above; LocationsSlave and LocationsByProvince are both occurances of the Locations table, and both <JoinSlave> and <Join> are linking two provinces together, correct?
                                  
                                 I also don't quite get what the end result of this would be if I successfully implement it.  Say I have Bob who lives in Los Angeles, which was part of Spain, Mexico, and the US (i.e. a many-many relationship); when I click on that button, how would it show me that information?  I think I am missing something conceptually about how a slave portal works; based on what you've written, I think that when I click the button it will bring up all the related records in that same portal at the next level... does it display them in the same portal?
                            • 11. Re: portal of a portal of a portal: managing nested join tables
                              philmodjunk

                                   JoinSlave is a second occurrence of the Join Table. LocationsSlave is an added occurrence of the Locations table.

                                   If set up correctly, Clicking the button for Bob should display the additional location info: Spain, Mexico, US in the slave portal. (You can include fields from LocationsSlave in the row of the portal to JoinSlave.)