Perhaps this will make more sense if you break it down to look at links to just one other table at a time.
You can place three portals on your actions layout to each of the three join tables so you can create links to the other records in all three of the other tables.
The other option is to set up a "star join" where all 4 tables (people, companies, actions jobs) form the points of the star with a single join table in the middle.
I'm really not sure which will be the better design for you here.
Because the Action might often be linked to at least three other tables [a conversation might be with a person about a couble of jobs, for example] I was envisioning a star join. But don't understand how the keys work for that.
But looking at the first option, are those three distinct TO for Action? or three <join tables> from one Actions table?
Have to mediate on this... At least there is a solution that isn't too too messy <s>. Thanks!
More, "I'm really not sure which will be the better design for you here." What would make on better than the other? Are there clear differences in the consequences of choosing one over the other?
The differences are pretty subtle, which is why I am uncertain as to which is the best for you.
With a "Star join", One join record links 0 or 1 action records to 0 or 1 People records to 0 or 1 job records to 0 0r 1 Company records.
To see all the linked records, for a given "Action" record, you'd use a portal to this join table. To link in records from the other three tables, would require placing three fields with drop down lists where you can select a person, job or company to link to your current action record.
With separate join tables, (and they could be three occurrences of the same join table), each join table links records only from one table to your Action record and you need only one drop down formatted field instead of three, but now you need three portals instead of one to see and manage all your links.
You may want to experiment with the different options here to see which works best for you--especially in terms of any summary reports you may want to generate from this data.
Thanks Phil, I am beginning to think separate joins might be better.. The star is complicating in that it makes a link even when there isn't one, and often there may be 1 or more links to an action. For example a meeting record might be linked to four people, one job and no companies. As for summaries, if that means counts of records, I am not expecting to need those.
I'll have time to experiment more in the next day or so, but it feels that the three join tables might be more flexible in the end. Most data entry would be in Action, with portals to manage links. I can also see the possibility to create an Action from one of the tables (people or jobs). Reports needed would be actions related to Jobs; or people and companies related to a type of action. or, when I get to be a real FM whiz, I might want to create individual actions each related to one of a found set of people, and a list including phone numbers.
"you need only one drop down formatted field instead of three" It is not obvious to me which this is. If enetering data in a layout based on Acion table, with three portals, don't I want a drop down for each portal/ related table?
it makes a link even when there isn't one
Not really. With either approach, you can link records on an "as needed" basis, but it can look like a specific person was linked to a specific job and then to a specific company-which would be confusing.
"For example a meeting record might be linked to four people, one job and no companies."
This can be done with either approach. With the star join, you'd have 4 join records that all have the same actionID to link them all to the same action record for that meeting. The first record would have a People ID and a Job ID. The other records would just have a people ID and ActionID.
With the separate join table occurrences, you'd have 5 records, four would record people ID's in the PeopleID field and One would record the Job ID in the JobID field. (Portal filtering can be used to keep each kind of join record separate for each Action record.)
Where you see a difference is if you want to produce a report based on this join table where you want to list all the people in one group, the companies in another and the Jobs in a third group--a report more easily produced with the separate table occurrences instead of a single "star join".
it can look like a specific person was linked to a specific job and then to a specific company-which would be confusing.
where you want to list all the people in one group, the companies in another and the Jobs in a third group--a report more easily produced with the separate table occurrences
I am going to push the separate joins further. It seems the better way to keep items distinct and be bale to manage them individually when needed.
Meanwhile I made some progress on this this weekend and have a working Action table connected separately to Jobs, People, Company. In rereading I may not have fully grasped the possibilities. I set up three different join tables. But, now having experimented with the pieces, I am beginning to understand what you describe above as one Join table with fields for ActionID, PeopleID, JobID and CompanyID, used in three TO iterations between Jobs/Ppl/Co components and Action. All combinatorial layouts would be based on the join tables? Is this a eureka?
I would base the portals on the join tables rather than the layouts.
Okay. I think I get what you are saying. [I tend to think with a pencil and sketchs]. Still puzzled by :" With separate join tables, (and they could be three occurrences of the same join table), each join table links records only from one table to your Action record and you need only one drop down formatted field instead of three, but now you need three portals instead of one to see and manage all your links."
What are you thinking is the One drop down formatted field? I have a drop down for each portal? Maybe we are going different directions?
The "one dropdown list formatted field" would be one of the three fields, but a different such field for each of the three portals.
Here's a demo file you may want to examine. It only links Contracts to Companies, but you can see how the portals are set up so that a portal to the join table on companies lists all its contracts and a portal to the join table on Contracts lists all the Companies for that contract.
That sounds/looks like where I came out. I guess the "only one" part of the description threw me off. Thanks, as always.