11 Replies Latest reply on Dec 20, 2013 1:03 PM by millerb

    Complex many to manys + self-join relationships


      I'm in the schema planning stage of a solution to track a law firm's cases and contacts, both current and historic. The cases frequently involve many interested parties. The major entities are:

      Cases (central entity)

      Organizations ("Orgs")



      All of these entities have direct relationships with each other that are all many-to-many. I understand this will require multiple join tables, multiple TOs, and various layouts to accommodate. I haven't implemented it yet, but I have faith that I'll find a way! The basic idea would be the following join tables:

      1) link Orgs to Cases according to "Role" in the case (Org_Case_Role),

      2) link Persons to Cases according to "Role" in the case (Person_Case_Role),

      3) link Persons to Orgs (Person_Org_Position).


      So far so good. All these relationships need to be tracked historically with regard to the case (so, for example, when a Person leaves an Org, I need to know the Org they worked for at the time of that case, not just their current Org). Since this Person to Org relationship is unique to the case, there would need to be another join table:

      - linking Persons to Cases to Orgs (three-way join): (Person_Org_Case)


      I think I've got that right, but I could be wrong and am very open to better solutions.


      Then it gets more complicated. Not only do multiple organizations and multiple persons have relationships to a particular case, but some of the organizations and persons have relationships with each other that are unique to the case. For example, a specific case might have multiple clients (Orgs and/or Persons). Each client might be represented by different lawyers (one or more Persons--never Orgs), each of whom work at different firms. The same goes for each opposing party (Orgs and/or Persons) who might each be represented by different lawyers who work at different firms. In addition to clients and opposing parties, there are many other roles played by Persons and Orgs in relation to any given case, but only clients and opposing parties have this issue where I need to track both the lawyers and the firms for each. If I simply list out all the Orgs and all the Persons related to a Case, even though I could use Person_Org_Position to drill down to the Persons related to those Orgs, or Orgs related to those Persons, both past and present, I still wouldn't have captured which lawyers link up to which client or opposing party.


      This suggests to me joins between four TOs, not just the two- and three-TO joins listed above. So:

      1) link Cases_Orgs_Persons_Orgs2 and

      2) link Cases_Persons_Persons2_Orgs

      where Orgs2 and Persons2 are separate TOs, allowing for self-join relationships between clients/opposing parties, lawyers, and firms.


      Am I on the right track? Would a join table with ID fields from those four TOs be able to track these relationships unique to a particular case? I can't think of another way to do it that doesn't risk potential duplicate/conflicting data and contact info.


      Luckily I don't have to represent this all on one layout, but I do have to be able to drill down to display these fourth-generation relationships for a given case (i.e. Case-->client/opposing party-->lawyer-->firm). I may be back with issues around the user interface, but for now I want to be sure I'm getting the structure correct.




        • 1. Re: Complex many to manys + self-join relationships

          I don't see why a single Role join table wouldn't suffice. It should care which tables it was connecting (hint: use UUIDs).


          Also, I'd add a couple date fields to that Role table with a Start and End, so you know when someone's tenure was with a particular case or organization.

          1 of 1 people found this helpful
          • 2. Re: Complex many to manys + self-join relationships

            Thanks, David. Yes, I'll be using UUIDs. The Start and End dates is a great idea. Sometimes someone's tenure doesn't last throughout a single case. I was going to use a Status field (past/current), but start/end date is much more informative.


            Having Org2 and Person2 as separate TOs will prevent any circularity problems, right?


            So if I understand correctly, the single join table would have the following fields (where Org1 and Org2 are separate TOs of the Org table, and Person1 and Person2 are separate TOs of the Person table):

            1) CaseID


            2a) Org1ID

            2b) Org1Role

            2c) Org1StartDate

            2d) Org1EndDate


            3a) Person1ID

            3b) Person1Role

            3c) Person1StartDate

            3d) Person1EndDate


            4a) Org2ID

            4b) Org2Role

            4c) Org2StartDate

            4d) Org2EndDate


            5a) Person2ID

            5b) Person2Role

            5c) Person2StartDate

            5d) Person2EndDate


            So some of these fields would be null, but I can still use this join table to call up any and all linked records from Orgs or any linked records from Persons and display the corresponding Role. Then I can drill down to each Org/Persons related Orgs/Persons and Roles, filtering for the CaseID. Is that correct?


            I assume there's no need to worry about overhead from null fields in the join table. Should I add a UUID for the records in the join table too, even though the multi-field combination is already unique? I'm not aware if there's a best practice on that.


            Does it matter which TO's fields are left null in this join table? I could go one route and assign a related Person or Org to the first available appropriate fields (set#2 above for Orgs, set#3 above for Persons). Then sometimes a lawyer would show up as Person1 in the join table (when the client is an Org), sometimes as Person2 (when the client is a Person). Likewise, sometimes a firm would show up as Org1 (when the client is a Person), sometimes as Org2 (when the client is an Org).


            Or would it be better to always assign lawyers to the Person2 field and always assign firms to the Org2 field, leaving either Person1 or Org1 null (depending on whether the party is Person or Org)? I'm thinking this second option might be better, having a little more structure. Maybe down the road I would want a layout that leaves off representing lawyers and firms altogether and just have portals listing Person1 or Org1. Then drill down to get the lawyers/firms. In this case, instead of Person2 the TO could be called "Counsel" and instead of Org2 the TO could be called "Firm" and they would correspond to something in reality. As of now, this issue of client representation is the only contingency I can think of where I need to link so many TOs. Does this approach make sense? Or would the added structure be unnecessary since I could always filter out lawyers and firms using Roles?


            Also, there will be a Contacts module that doesn't need to deal with cases. I would have a separate "Position" join table for the Contacts module that just joins Persons and Orgs (with Start and End dates). "Position" seems different than "Role" in that it only applies to a Person in relation to their employer. Is that correct?


            Thanks again.

            • 3. Re: Complex many to manys + self-join relationships

              Just getting back to this again. I can't use UUIDs because my institution won't upgrade to FM12, unless there's another easy way to generate them without the Get command. I'm not a programmer, just staff working on this in-house. UUIDs aren't really essential for this solution, are they? Aren't they mainly for when there's a lot of simultaneous record creation, including remotely? Or am I misunderstanding their usefulness for this join table? For this database, which will never be very huge, could I just use a five-digit serial ID, starting in a different range for each primary key field?


              It turns out there are a variety of contingencies and combinations of Persons and Orgs to accommodate, so I needed to conceptualize the join a little differently, though the structure looks basically the same. Here are the fields I'm planning now, where either the MainOrg fields or the MainPerson fields will have values in a given record (never both in the same record). Related Orgs and Persons, whether counsel, firms, billing contacts, organizational reps, etc., will go in the RelPerson and RelOrg fields.


              • 4. Re: Complex many to manys + self-join relationships

                Okay, I'm new to FM and still hoping for further guidance. I will be able to upgrade to FM12, so that will take care of UUIDs.


                Apart from the multiple people-organization relationships mentioned above, this solution has another self-join issue. Some cases are reciprocally related to other cases (for example, separate cases around a similar issue, or cases and their appeals). I don't need to specify what the relationship is, I just want to be able to list out related cases, so the relationship is symmetric. This means a many-to-many self-join between, say, CASES and RELATEDCASES (a TO of CASES). I would like to be able to display related cases in a portal, and also to create/delete the symmetric join relationship via a portal.


                In the forums, the closest situation I found was discussed in 2005: http://fmforums.com/forum/topic/38282-join-table-inverse-relationships/

                Then it was decided that the only way to do this is to use either:

                a) a single multi-key join field, with a bit of calculation and scripting, or

                b) two separate key fields (for each TO) and two separate records for each self-join relationship (with scripts creating/deleting the second record which is necessary to make the relationship reciprocal/symmetric).


                One solution using the first option was offered (the one called 1131819296-SymmetricSelfJoin.zip). Has anyone come up with a better/different way to handle this situation?


                Also, I'm having trouble figuring out which field(s) from which TO to put in the portal (with which value list), since the actual keys will be UUIDs hidden to the user. I've discovered there's the risk of accidentally replacing IDs with other data, and vice versa in a self-joined portal! Ideally, I would want to match on the UUID, but display in the portal only:

                Case No. (this is not the UUID) and Case Name


                Then I would want users to be able to create new portal records using a drop-down list of Case Nos. (first field) and Case Names (second field).






                • 5. Re: Complex many to manys + self-join relationships

                  Jackie -


                  To handle the issue of users screwing up key fields (a real possibility), one method would be to spawn a separate List view window. You then allow the user to click on the proper selection, which fires a script that stores the key in a variable, does whatever setting of proper keys it needs to, and then takes the user back to the original interface.


                  To make it easier on the user with long lists, you can implement a filtering mechanism. This is usually done via a global field in the header with a Script Trigger that performs a Quick Find on the List view layout.


                  By doing things this way, you can put only the meaningful fields (name, description, whatever) in the portal and never show the keys to the user at all.





                  1 of 1 people found this helpful
                  • 6. Re: Complex many to manys + self-join relationships

                    Thanks, Mike. Yes, that helps. I can see that a separate selection window would be better here. Then it wouldn't really matter which TO's fields are displayed in the portal since they can't be edited there. Although I wish I understood the join well enough to figure out which TO's fields should be in the portal hypothetically.


                    I'm also still not sure how to set up this symmetrical self-join with a join table, whether method (a) or (b) above or some other method. I'm leaning towards method (b), which seems cleaner, but I'm just guessing.



                    • 7. Re: Complex many to manys + self-join relationships

                      I tend to prefer method (b). It allows you to keep your relational integrity cleaner (e.g., you can enforce referential integrity and not worry about blowing something away you shouldn't). Single-value keys are also easier for me to wrap my brain around.  



                      • 8. Re: Complex many to manys + self-join relationships

                        Excellent, good to know my guesses aren't too far off base!

                        • 9. Re: Complex many to manys + self-join relationships

                          Here a post in french with examples on solving a many to many relation.


                          • 10. Re: Complex many to manys + self-join relationships

                            Thank you. I was able to get the French translated, but not the file, since I don't have FM12 yet (soon!).


                            I ended up going with the stacked fields solution: https://fmdev.filemaker.com/message/67721#67721

                            Turns out it wasn't the self-join that was the problem with the fields, just a standard many-to-many portal issue. I will have to script to create the reciprocal record for the self-join, though. Next task!

                            • 11. Re: Complex many to manys + self-join relationships

                              Thought I'd follow up for the record with my final solution. I couldn't put all the relationships into one join table without running into problems with unpredictable, probably buggy, portal behavior. Say the same case (called "matter" below) has related main organization contacts and main person contacts, each in a separate record in the join table. In a record linking to a main organizational contact, the main person fields are empty, and vice versa. But then things get weird. In a portal from Matters showing just the main organization fields, only those records with main organization fields created prior to any record with main person fields--and therefore no main organization fields--appeared in the portal. Any additional records created later with main organizational data failed to appear, no matter what the sort order of the portal. Only record creation date (which also determines sequence of auto serial ID) mattered. I can only guess that this behavior is a bug (FM11) because it makes no logical sense that a portal should care about record creation date or serial ID when they are not even part of the sort order or relationship.


                              Anyway, the solution below is probably more correct because it avoids both duplicate data and empty fields in the "main" fields of the table joining Matters to main contacts. In the end, there are three join tables:

                              1) ROLESOrg, linking matters to main organization contacts

                              2) ROLESPerson, linking matters to main person contacts

                              3) ROLESRel, linking any of the main contacts in the previous two join tables to secondary contacts. Each record in ROLESRel accommodates up to one person and one organization (e.g. a lawyer, and that lawyer's firm, who legally represent the main contact). There are two occurrences of ROLESRel, named below OrgROLESRel for layouts based on ROLESOrg, and PersonROLESRel for layouts based on ROLESPerson.

                              As shown in the graph, Matters_Contacts is the TO of Matters wrt Contacts. (Ignore the field "Matter ID" at left--it's not a key field.) There are three occurrences each of the related tables Organizations and People.




                              Matter_Contacts Relationships Graph.png