Many-To-Many Relationships

A client called me with a problem today. He has a custom object called "Projects" in which he keeps track of the construction projects his company is working on. He also keeps his contractors in the system as Accounts and Contacts.

My client wanted to be able to create a link on the Account record to the Project Record to show which Projects a given contractor is working on. The problem he ran into appears when a contractor works on more than one project. Many contractors can link to the project record, but one contractor can't link to many project records.

The problem is based on the one way link. Since the contractor (an account record) has only one field pointing to the project record, only one project can be assigned per contractor. We have what is called a "One-to-Many" relationship and we need a "Many-to-Many" relationship.

While the functionality is not "built-in" to salesforce.com, there is a way to solve this problem. The solution is to create another object that serves as a link between the contractor and the project. We called this object "Project Contractor". Each "Project Contractor" record will have two fields, a lookup relationship field associated with the Project Object and another lookup relationship field associated with Accounts. Each record will, in essence, be a relationship between a particular project and a contractor. We name each record according to the project and the name of the contractor separated by a hyphen, for example, "Downtown Building-Jones Construction".

Using this third object allows there to be a related list on both the Account Detail Page and the Project Detail page called "Project Contractor". Each related list can show multiple records. Therefore, a given contractor record (an Account record) can show on its related list all of the Projects that the contractor is working on. Then on a given Project record, there will be a related list that shows all of the contractors that are working on that project.

You can move between the two records (contractor and project) via the Project Contractor record contained in the Project Contractor Object that will be found on the related lists of the Account and Project detail pages.

In this way we have a many-to-many relationship link between the Account record and the Project record. Very, very cool.