We are testing out ideas to create a script to disable a relationship for a list of contacts, as that doesn't seem to be an option under Actions from a Search output list.
To get a feel for what we want to do, we have a script that generates an SQL query to disable the relationship for a list of contact_id's. Works fine.
However, this relationship is also tied to an inherited membership. That membership is not deleted, so we wanted to construct a parallel SQL query to delete the corresponding membership.
Can someone explain the database structure that allows us to link the two? In the civicrm_membership record, we see the membership_type_id that would tell us which record(s) are possibilities for the ones to delete. However, our members may have more than one corresponding inherited membership with the same relationship_type_id, because it is common for members to belong to two or more organizations, so they may have 2 or more memberships that are distinct and inherited.
A side fact is that the UI action on an individual view to disable or delete the relationship does ALSO remove the membership, so we know it is possible! We are essentially trying to automate this action for a list of contacts.
Another odd fact is that, if we delete the relationship, but still have the dormant membership, if we add the relationship back, then we have TWO memberships in the database, each related to the same organization. Their civicrm_membership records are identical. (If we do it again, we of course now have 3 ;) ) I was hoping there would be a column which would be NULL except for inherited memberships, where the column would contain the corresponding contact_id for the relationship. The membership END DATE provides a "weak" clue, but it is possible that 2 parent organizations could have the same END DATE.
I noticed a JIRA issue that is similar, https://issues.civicrm.org/jira/browse/CRM-16087. But that was evidently solved.
I also ran the Scheduled Job to Update Membership Status, hoping it would remove inherited memberships when the corresponding relationship is disabled or deleted, but that didn't seem to have effect.
Any help in understanding the full linkage for inherited memberships within the database is appreciated. We've been running CiviCRM for about a year now and are finally digging into some of the internals. Our ultimate goal is to provide an addition to the core or an extension so that the Action of disabling a relationship (and removing any inherited memberships based on that relation) is an available Action when viewing any list of contacts in Civi. It is too painful to disable a list of hundreds manually via the UI, although it works.
running Civi 4.7.22 on WP 4.8.2