A many-to-many relationship described the situation when we have a need to create a link between several records in one table with several records in another table.

Imagine a new custom entity called 'Event'. For example, a customer may have a need to describe the link between a person and an event. A person can attend various events, and those events may be attended by many customers.

So how can we relate multiple persons and multiple events?

The first method is to use the relationship feature within Sage CRM.

The Related Entities Feature

The Related Entities feature provides a simple to use and very powerful way of creating ad-hoc relationships between main entities within Sage CRM. A system administrator can easily create a new type of relationship between a company, a case or a custom built entity without having to write a single line of code.

This short video explains how the RelatedEntities feature is used to define relationships.

These defined relationships can then be used in reports and customizations can be based on them.

See this article about Codeless Customization. "Extending the RelatedEntities feature using RunBlock"

But you may want to do more with the relationships that the inbuilt behaviour allows.

If I return to the example of the need to relate multiple persons with multiple events. And events here is a new custom entity in the system.

We need to be clear about the behaviour we want.

• A person may attend many events.
• An event may be attended by many persons.

The above is probably not correct. It is likely that the events are not 'shared' directly between persons but rather... you have an instance of event registration and that is distinct because it has properties like a subject, or a location or a date etc

So a better thing may be

• A person may have many EventRegistrations
• A product may have many EventRegistrations

This gave us the idea that there needs to be an intersection entity between the person and the event.

We also need to recognise that Relational database systems don't allow like a direct many-to-many relationship between two tables.

And if you then consider our special need to think about integration with accounting systems then we only need to think about keeping track of invoices. If there were many invoices with the same invoice number and one of your customers inquired about that invoice number, you wouldn't know which number they were referring to. This is one reason for assigning a unique value to each invoice.

This is why we tend to resolve the many-to-many relationship into two one-to-many relationships by using a third table, called an intersection entity or join table. Each record in an intersection entity includes a match field that contains the value of the primary keys of the two tables it joins. (In the intersection entity, these match fields are foreign keys.) These foreign key fields are populated with data as records in the join table are created from either table it joins.