The Many-to-Many relationship is unique in that, unlike the One-to-One and One-to-Many table relationships, the Many-to-Many relationship requires three tables instead of two. This is because in Ninox, the relationship between two tables, whether it be Parent-to-Parent, Master Data:“Transactional Data”:https://www.manula.com/manuals/ninoxus/ninoxus/1.0/en/topic/transactional-data-tablesor Parent/Child always has a single record, in at least one of the related tables, as a party to the relationship. In a Many-to-Many relationship, both “parties” in the relationship consist of multiple records. For this reason, a third table – the Cross-Reference Table – is required to facilitate the relationship between the two other tables.
Examples of Many-to-Many relationships include:
- A university where there are many classes offered and each class can have many student attendees while, at the same time, each attendee may be enrolled in multiple classes
- A professional sporting event where there are many events being hosted and each event has many competitors while, at the same time, each competitor may be signed up to participate in multiple events
- An office park where there are many office buildings being constructed and each building has multiple contractors working on it while, at the same time, each contractor is working on different projects in multiple buildings
A Many-to-Many relationship is represented in the image below.
In the image above, the Class Roster table is the Cross-Reference Table that enables the concurrent relationships between multiple classes and the multiple students enrolled in each class. Using this triangulated structure, it is possible for Ninox to keep track of all of the students and their class schedules while also keeping track of all classes being offered with a listing of who is attending each class. As both parties (classes and students) to each relationship represent multiple records, the intermediary ‘Class Roster’ table is required.