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