12 Replies Latest reply on May 24, 2011 9:32 AM by margotjacqz

    many to many to many thicket

    margotjacqz

      Title

      many to many to many thicket

      Post

      Having a lovely time on my first fm database. I appar to have main parts up and working, but wandered into the marshes today on the above subject. There is still something I am not getting about linkages. Here's the basic outline, and I hope someone can help with the principles as well as details. I thought I had a grasp, but it's not fitting.

      Three basic information tables: People, Companies, JobsIamWorkingOn(Recruitiig).  What I want to add is an ActionTable which will be the daily diary of notes, correspondance, telecons, proposals, meetings, to follow up, etc.  Each Action will have a subject, text, status, dates. Each Action might be related to one or more people, one or more companies, one or more jobs, in multiple combinations. And, of course, all of the above will have many actions. So many to many and a JoinTable. But something's not working..

      I am perhaps muddled about link keys. And then how to build the new data entry layout for an action and linking it. So backing up, what's the foundation and where are the traps?

      Thanks as always. margot

        • 1. Re: many to many to many thicket
          philmodjunk

          Perhaps this will make more sense if you break it down to look at links to just one other table at a time.

          Actions---<Action_People>----People

          Actions----<Action_Jobs>----Jobs

          Actions----<Action_Companies>-----Companies

          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.

          • 2. Re: many to many to many thicket
            margotjacqz

            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!

            • 3. Re: many to many to many thicket
              margotjacqz

              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?

              • 4. Re: many to many to many thicket
                philmodjunk

                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.

                • 5. Re: many to many to many thicket
                  margotjacqz

                  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?

                  • 6. Re: many to many to many thicket
                    philmodjunk

                    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".

                    • 7. Re: many to many to many thicket
                      margotjacqz

                      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.

                      Thanks !

                      • 8. Re: many to many to many thicket
                        margotjacqz

                        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? 

                        • 9. Re: many to many to many thicket
                          philmodjunk

                          I would base the portals on the join tables rather than the layouts.

                          • 10. Re: many to many to many thicket
                            margotjacqz

                            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?

                            • 11. Re: many to many to many thicket
                              philmodjunk

                              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.

                              http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html

                              • 12. Re: many to many to many thicket
                                margotjacqz

                                That sounds/looks like where I came out. I guess the "only one" part of the description threw me off.  Thanks, as always.