Problem: If you would like to create a “Many to Many” relationship between objects you must create a linking object in the middle. The linking object can contain more then the ID fields, but primarily contains the IDs of the objects that it is linking. This poses a HUGE problem with reports, related lists, etc, even with report types. There is no way to show or report on the Many to Many relationships. You can only report on one of the primary objects and the linking object. Without de-normalizing the data and saving fields to the linking object reports are very limited.

One Current Solution: To overcome this we have had to de-normalize the data model. We take fields from both primary objects and replicate them on the linking object creating a composite object. This has to be done with APEX, AJAX or some code external to Salesforce. It is very cumbersome to code this type of solution and challenging to ensure data integrity when one of the primary objects data changes. Also, if future reporting requirements change we have to code, not configure, a solution.

One Proposed solution: Create a field type called link or “Many to Many”. Under the covers Salesforce could create the linking object storing the IDs from the two primary objects. Then Salesforce could expose this in reports, related lists, etc. Since Salesforce created the linking object the system would have more control over the data model and create the composite view.

Thanks,
Todd Bursey
Appirio