portal of a portal of a portal: managing nested join tables
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.