Many to Many Relationship Manager - adaptdev/KenticoContrib Wiki

The Many-to-Many Relationship Manager form control allows specifying multiple associations with custom table items. It is similar in functionality to the built-in Multiple choice form control, but it persists the relationships in a third join table (also called cross-reference or junction table) instead of in a delimited string directly on the object, as the Multiple choice control does. Additionally, it stores a count value on the object, indicating the number of relationships assigned.

The normalized data organization supported by the Many-to-Many Relationship Manager has several benefits including better data integrity and more efficient querying. This data structure might also be beneficial for working with data from third-party systems that already organize their data in this way.

Example

In the following example, we will be using the Many-to-Many Relationship Manager to create relationships between recipe documents and recipe types (e.g., Appetizer, Main Course, Dessert, etc.), for a cooking and recipes website.

Recipe is a custom document type. For the purposes of this example, it is assumed this document type has already been created. See Defining a new document type for more details.

Step 1 – Setup related data table

The first step is to setup the custom table that will house the related data. For this example, the related data is Recipe Types. See Creating custom tables for more details.

At minimum, this table should have and ID (integer) field and a Name (string) field. Note the names of these two fields for later use.

Now, populate this custom table with some data.

Step 2 – Setup join table

Next, we need to setup the custom table that will house the join data.

At minimum, this table should have at ID field for itself (required by Kentico), and two more integer fields to store the IDs of the related entities. In this example, we call these fields RecipeID and RecipeTypeID.

Step 3 – Import the form control

Now we need to import the Many-to-Many Relationship Manager form control. See Importing a site or objects for more details.

Make sure the Import files option is selected, before performing the import.

Step 4 – Add and configure the form control

Next, we add add a field to the Recipe document type that will utilize the new form control. The field itself can be named anything. In this case, we call it Type.

The Attribute type should be set to Integer number or Long integer number, as this field will be used to store a count of the assigned relationships.

Allow empty value can be checked or not; it doesn't matter.

For the Form Control option, select Many-to-Many Relationship Manager. If you don't see this option in the drop-down, go back and make sure the import in step 3 was successful.

After selecting the form control, fill out the Editing control settings as follows:

  • Related Data Table: Select the custom table that houses the related data for this relationship, setup in step 1. In this case, Recipe Types.
  • Name Field: Enter the field from the related data table that holds the display name. In this case, Name.
  • Join Table: Select the custom table that houses the join data for this relationship, setup in step 2. In this case, Recipe Types (Join).
  • Left Key Column Name: Enter the field from the join table that holds the IDs for the documents in this relationship (the left side of the relationship). In this case, RecipeID.
  • Right Key Column Name: Enter the field from the join table the holds the IDs for the related custom table items in this relationship (the right side of the relationship). In this case, RecipeTypeID.

Optional Step – Validation

Since the relationship count will be stored in this field, the built-in validation can easily be utilized to enforce a minimum or maximum number of selected items. In this example, we require at least one recipe type be selected by setting Min value to 1 and setting an appropriate error message.

Step 5 – Use the form control

Now when a new Recipe document is created, the Recipe Types are listed as selectable options on the Form tab for the document.

After saving the example document above, we can see how this data is persisted in the join table:

RecipeID here contains the DocumentID for the recipe document.