Many-to-many-to-many?

[Ian Beatty]

Greetings.

I'm just coming back to FM after a long hiatus (since version 4, I think!), and I'm trying to get my head around the way it does relationships. I've got a problem that would be doable with SQL, I think, but I can't figure out how to approach it with FM 7. It seems like it ought to be a standard kind of problem, so maybe one of the Wise Ones here can point me in the right direction.

I've got what I'd describe as a many-to-many-to-many relationship. I've got a table for "people" in my organization; each row contains a person's name, email address, phone number, etc. I've got a second table for "roles": chair of the hiking committee, secretary of the paddling committee, webmaster, newsletter assistant editor, etc. The relationship between these two tables is many-to-many, since one person frequently fills more than one role, and sometimes two people share one role and some roles have many people ("kayaking trip leader"). So, I created a join table "people_to_roles".

I also have a table for "groups": various committees, working groups, classes of trip leader, etc. The relationship between roles and groups is also many-to-many, so I created another join table: "roles_to_groups".

My problem is, how do I get a list (report) of all the *people* (and, say, their email addresses) associated with a particular *group*? I need a mechanism that finds all the roles matching that group (easy), and then all the people matching each of those roles (not easy), uniquing the final list. Oy. And I also need to go the other way: get a list of all the groups a particular person is associated with.

Logically, the two join tables and the "roles" table act like one *implicit* join table between people and groups. Is there any way I can use it this way?

Thanks for the wisdom.

Cheers,

..Ian

Berkshire Chapter, Appalachian Mountain Club

http://amcberkshire.org

[Fenton]

I see now you want Groups to Roles to be many-to-many. What that means is that you must have BOTH Role ID and Group ID as foreign keys in your People to Roles table.

And the relationship between the two "join" tables must be compound, with both keys. You would look from People to the Roles to Groups join table, to see a person's groups. You'd look from Groups to the People to Roles table to see the people in a group.

PeopleRolesGroups.zip

[Martin Brändle]

As I understand, you have the following scheme:

group - group_to_roles - roles - roles_to_people - people

In FM7, in a portal you can show a field of an table external to this portal, e.g. in the people-roles_to_people portal you can also show a field of the group table and sort on this field.

Another idea would be to create a join table with three secondary keys group id, role id and people id that star-like connects your group, role and person table:

people - connect_table - groups

                       |

                    roles

E.g. you can then do a search of one category in the connect table and sort the results by the other two categories. I use that scheme in another context, and it works well.

Of course this scheme can be extended to many more categories or many-to-many-to-many-to-... relationships, respectively.

Martin

[Ian Beatty]

Fenton, I'm not quite sure I follow... Are you saying basically the same thing as Martin? That is, that I need a three-way join table?

I see how this would let me do the searches I need. However, it does seem less-than-optimal, since I can't just add a particular role into a new group without finding and modifying the join table record for every person in that role. There's redundant information, since now the fact that *every* person with Role J is in Groups Y and Z isn't coded in one spot, but distributed throughout the three-way join table.

Martin, you say

in a portal you can show a field of an table external to this portal, e.g. in the people-roles_to_people portal you can also show a field of the group table and sort on this field.

AFAIK, I can only see one match from such an external table, i.e. only one group that the person belongs to. That second many-to-many in the chain is the killer.

I gather you're both saying that the schema "group - group_to_roles - roles - roles_to_people - people" isn't going to cut it with FM7 (unlike SQL), and I'll have to throw extra keys in somewhere and just deal with the maintenance overhead. Correct?

Thanks for the advice,

..Ian

[Martin Brändle]

I see how this would let me do the searches I need. However, it does seem less-than-optimal, since I can't just add a particular role into a new group without finding and modifying the join table record for every person in that role. There's redundant information, since now the fact that *every* person with Role J is in Groups Y and Z isn't coded in one spot, but distributed throughout the three-way join table.

No, not quite correct. If you add a particular role to a new group, you have to ADD records in the three-way join table, not to modify them.

With respect to redundancy you might be right. However, the linear scheme persons <-> roles <-> groups always requires that a person must have a role to be in a group. The star scheme allows persons without roles to be in a group; it allows to assign roles to groups without having any persons attributed to them; in addition it avoids the circular relation persons <-> roles <-> groups <-> persons which in any relational database is not allowed.

Martin, you say

AFAIK, I can only see one match from such an external table, i.e. only one group that the person belongs to. That second many-to-many in the chain is the killer.

Yes, you are right, sorry. I don't think that one can show a portal within a portal (never tried that out), which would save your problem.

I gather you're both saying that the schema "group - group_to_roles - roles - roles_to_people - people" isn't going to cut it with FM7 (unlike SQL), and I'll have to throw extra keys in somewhere and just deal with the maintenance overhead. Correct?

Thanks for the advice,

..Ian

[Fenton]

Here is the same thing, without the compound relationship. It uses more TOs.

I don't really know what anyone is saying, including myself :-] It all depends on what you're trying to do. And I don't really understand exactly what that is. If you're saying that a "group" is just an abstract concept, an abstract container of "roles," then you don't need a GroupID in the PeopleRoles table (except maybe as a global field, to filter roles).

This seems to be what you're saying, because of the "I can't just add a particular role into a new group without finding and modifying the join table record for every person in that role" statement. If you want a person's choice of a role to automatically be associated with every instance of a group with that role, then obviously a "role" is not a "unique position in a specific group," but just an abstract concept (like group).

(BTW, even if it was unique, you could still do reports on "all secretaries" or whatever, in the GroupRoles table.)

Normally you would not expect the assignment of a role to automatically associate a person with any group that had that role. Nor would the creation of a new role (i.e., position in a specific group) automatically be assigned to everyone with a similar position in other groups.

Basically you need to specify whether the things you're talking about are abstract or concrete. Our language doesn't really differentiate between them when using words like "role" and "group."

PeopleRolesGroups2.zip

[Fenton]

Here is a strange abstract version.

PeopleRolesGroups3.zip

[Ian Beatty]

I'll have to scratch my head over the relationship diagrams you posted.

Maybe my problem will be a little clearer if I use a concrete example. I've got a person, Jimmy Carabiner. He's the chairperson of the Mountaineering Committe. That means I want to assocate the role "Mountaineering Committee Chair" with the person Jimmy Carabiner. Now, being the chair of that committee means that Jimmy is a member of two distinct groups: the Mountaineering Committee, and also the organization's Executive Committee. If I have a table that maps people to roles, and another that maps roles to groups, then if Jimmy steps down from that position and Abby Prussik becomes the new Mountaineering Committee Chair, I only have to replace one entry in the people-to-roles link table and Abby is automatically associated with all the appropriate groups.

Meanwhile, Lenny Longstrides and Nate Vibram are co-chairs of the Hiking Committee. So I've got two entries in the people-to-roles link table, one from each of them to the role "Hiking Committee Chair". That position is also a member of both the Hiking Committee and the Executive Committee, so there are two links in the roles-to-groups link table, one for each committee.

Now, setting up that structure -- people, people_to_roles, roles, roles_to_groups, groups -- is straightforward. And I can easily generate a report of all the roles a particular person holds (often more than one), all the people associated with a particular role, all the roles attached to a particular group, or all the groups a particular role belongs to. What I cannot figure out how to do is find out every *person* belonging to the Executive Committee, or every group that Jimmy Carabiner is a member of by virtue of one role or another.

I've considered just having tables for people, people_to_groups, and groups, where the link table has an additional field for the role of the person in that group. Problem is, for Jimmy Carabiner, his role on "Executive Committee" is "Mountaineering Committee Chair" and his role on "Mountaineering Committee" is "Chair" -- essentially the same thing. If I generate a list of all the roles Jimmy has, I'll see two, but they are realy the same thing; he only has one role in reality. As an additional problem with this design, some roles aren't members of any particular group; they're just roles within the overall organization.

*Whew!* Have I bored you yet?

Thanks for the thoughts,

..Ian

[RalphL]

Maybe my problem will be a little clearer if I use a concrete example. I've got a person, Jimmy Carabiner. He's the chairperson of the Mountaineering Committe. That means I want to assocate the role "Mountaineering Committee Chair" with the person Jimmy Carabiner. Now, being the chair of that committee means that Jimmy is a member of two distinct groups: the Mountaineering Committee, and also the organization's Executive Committee. If I have a table that maps people to roles, and another that maps roles to groups, then if Jimmy steps down from that position and Abby Prussik becomes the new Mountaineering Committee Chair, I only have to replace one entry in the people-to-roles link table and Abby is automatically associated with all the appropriate groups.

It looks like you have 4 actions that take place: 1) Jimmy is removed from Executive Committee (a group), 2) Jimmy is removed from Mountaineering Committee Chair (a role), 3) Abby is added as Mountaineering Committee Chair (a role) & 4) Abby is added to Executive Committee (a group).

I don't see this as a major operation. I see Role and Group as value lists, very little data. People will have data. And if you use the star join as suggested earlier it would contain a lot of data. A portal in People would show what groups they were in and what role they had in the group. A portal in Group would show the people in the group and their roll. I don't see see a need for a portal in Roll. Reports would be priinted from the join table.

[Ian Beatty]

It looks like you have 4 actions that take place: 1) Jimmy is removed from Executive Committee (a group), 2) Jimmy is removed from Mountaineering Committee Chair (a role), 3) Abby is added as Mountaineering Committee Chair (a role) & 4) Abby is added to Executive Committee (a group).

Two more actions, actually: Jimmy is removed from the Mountaineering Committee (a group) and Abby is added to it.

I guess what makes me uncomfortable with this is the potential for my database to get into an inconsistent state. I have to make sure that if Jimmy is removed from his role, he is also removed from the corresponding groups at the same time. Which means the good old "Delete Record" command, or deletion through a portal, is off-limits; I'd better make sure every link deletion is done by a script that looks up and deletes all corresponding links to other tables. Ditto for link creation.

The star-join approach sounds preferable, in that instead of having two separate join tables that must stay synchronized, I've just got one. The down side is that it's got redundant information: there must be two join table entries that connect Jimmy with the role "Mountaineering Committee Chair", one linking to the group Mountaineering Committee and one to the group Executive Committee. Now, portals and reports that generate a list of roles must worry about uniqueness of the displayed list.

Pick your poison, eh?

..Ian

[Fenton]

If you want to see only "unique" roles for a person, you could use the ValueListItems (Design) function, which removes exact duplicates, in an unstored calculation field, filtered via the relationship. The multi-line result can be coerced to comma-separated, if that's better for layout disply or printing. It's not a very "useful" list, as you can't click on it, like a portal.

Once you accept that any kind of transfer, or even deletion has to be scripted (because of the many-to-many),* then it's mostly a matter of creating foolproof scripts and an interface to run them.

Just be glad you're using version 7, where it can happen within one script, by changing layouts, rather than in 6, where you'd be jumping between files :-]

*In my file with the compound relationship between People-Roles and Group-Roles, you can turn on "Delete related records" in both directions, and it works without a script, deleting in both tables, via the portals in either People or Groups. This assuming you're not using Roles as a "value list table" (which is different). The "Allow creation of related records" option does not work however, because only one of the fields has a value at creation time.

[RalphL]

Two more actions, actually: Jimmy is removed from the Mountaineering Committee (a group) and Abby is added to it.

Why must Jimmy be removed? He is giving up the chair. Couldn't Abby already be a member?

In the join table there is a record that shows Jimmy, Mountaineering Committe, Chairperson. There is another that shows Jimmy, Executive Committe, Member.

[RalphL]

Another idea came to me, instead of removing and adding people to a group or a roll how about adding dates that they took that roll and left that roll. Now you can have a history in both the people and group tables. You can still have portals that see only the current conditions.