Lookup Relationship: One to Many where Child can belong to Multiple Parent

Question

I wish to have a Gym object that contain zero or more members as a related list. So Gym is the master and Member is the detail side - but the Member records does NOT have to be linked to any particular Gym.

A given Member record can be attached to 0 or more Gym

Basically, when I create (or edit) a Gym record, I want to be able to attach 0 or more Member records to that Gym record.

In the related list page block - I do NOT want the New button to create a NEW Member record - instead, I wish to do a lookup of Member records and to attach as many of these as I require for the master Gym record (not just a single lookup - as per the Account lookup when I create a Contact lookup)

What is a good way to implement this ?

Answer 1

What you've described is not a one-to-many relationship, but rather a many-to-many relationship (a Gym can have many members, and a member can be associated with many Gyms).

To capture this in Salesforce, you'll need 3 objects. You already have 2 objects, Gym__c and Member__c. The third object sounds like it'll be Membership__c.

This concept is called a junction table, though in Salesforce it's known as a Junction Object. Membership__c would have a relationship to both Gym__c and Member__c.

In the Salesforce documentation, they say to make Membership__c be the master in two Master-Detail relationships (Gym__c and Member__c being the 'detail' sides of those Master-Detail relationships). Based on you saying that a Gym can have 0 members, and a member can have 0 gyms, this would be a lookup relationship instead.

If you continue on reading through that documentation link, it goes on to say that you could include fields from Member__c in the related list on Gym__c (and vice versa), even though the related list is to Membership__c. You might only be able to do this if you make Master-Detail relationships though (I'm not able to test this out myself at the moment).

Adding multiple members to a gym sounds like a separate question to me. This question may be a good place to start

Answer 2

Seems you are talking about Junction object. Where you can define many to many relationship.

I would suggest you to create object Called "Gym Member" and make Gym object and Member object to its child. Please see below picture which shows many to many relationship.