trigger campaignMemberRelationshipTrigger on CampaignMember (after insert, after update) {
  Map<Id,Campaign> campaigns = new Map<Id,Campaign>();
  Map<id,Contact> contacts = new Map<Id,Contact>():
  Map<id,relationships__c> relationships = new map<id,relationships__c>();
  Map<Id,Relationships__c> updateRelations = new Map<Id,Relationships__c>();
  
  for(CampaignMember cm:Trigger.new) {
    campaigns.put(cm.campaignid,null);
    contacts.put(cm.contactid,null);
  }

  campaigns = new map<id,campaign>([select id,account__c from campaign where id in :campaigns.keyset()]);
  contacts = new map<id,contact>([select id,relationship__c from contact where id in :contacts.keyset()]);

  for(contact c:contacts.values())
   relationships.put(c.relationship__c,null);
  relationships = new map<id,relationships__c>([select id,status__c,contact__c,list_owner__c from relationships__c where id in :relationships.keyset()]);

   for(CampaignMember cm:Trigger.new) {
     contact c = contacts.get(cm.contactid);
     relationships__c r = relationships.get(c.relationship__c);
     campaign cmp = campaigns.get(cm.campaignid):

     if( cm.opt_out__c && r.list_owner__c == cmp.account__c ) {
       r.status__c = 'Opt-Out';
       updateRelations.put(r.id,r);
     }
   }
   update updateRelations.values();
}

This was pretty fun.

Original Post:

Total queries: linear (3n).

Total DML: linear (n).

Suggested solution:

Total queries: constant (3).

Total DML: constant (1).

Please let me know if I messed up the translation anywhere, or if there are any bugs.

Cheers!