Creating Many to Many Relationships
This topic discusses the design factors and decision points when you want to create a many-to-many relationship between records in one table and those in another table – or even the same table. This relationship is generally created by using a linked field on one side and a related table field on the other.
Terminology
The following terms are used in this article:
Linked field | Linked field is an umbrella term for several data types that create a link from one table to another, most commonly the link to selected fields or the link to single field data types. |
Embedded table | Embedded table is an umbrella term for any data type that displays a list of records in table format. These include the related table and the link to selected fields with MVE data types, as well as the embedded search result and communications search result data types. |
Active link | An active linked field means that users are expected to interact with the linked field to find, select, and link to records. |
Passive link | A passive link means that the field is primarily informational and displays records that have already been linked by the system, such as by a rule, saved search, or other automation, as opposed to by users. |
MVE | Stands for multiple values enabled. When a link to single field or link to selected fields field holds multiple records, it is referred to as a linked field with MVE. |
Creating Many-to-Many Relationships
There are often situations in which you want to create a many-to-many relationship between two tables. This is done by pairing either a link to single field with MVE or a link to selected fields with MVE field in one table and a related table field in the other. The challenging part of the design is deciding which table holds which field type.
This article will discuss how to decide which table in the relationship should contain the active linked field. Active in this case means the field in which the user is going to actually choose the records that should be linked. In contrast, the related table is a passive data type: it is automatically populated with the records that were linked to it from the other table.
The link to selected fields with MVE data type looks exactly like a related table, since they both present an embedded table on the screen, but they behave differently. It is important to put each data type in the appropriate table to accurately meet the business requirements.
The following discussion covers the differences in behavior between the linked field, either the link to single field or link to selected fields, and the related table. In particular, we will focus on the subtleties of the link to selected fields with MVE.
Before reading this document, please review Linking to Multiple Records, which covers the differences between a link to single field with MVE and a link to selected fields with MVE. That article will help you decide whether the active linked field will be a link to single field or a link to selected fields.
Typical Use Cases
Here are some typical examples of a many-to-many relationship between tables
- An asset may be linked to multiple Contracts and a Contract may be linked to multiple Assets.
- People may be linked to several Opportunities and vice versa.
- A Customer can buy and be linked to multiple Products, and a Product is purchased by multiple Customers.
- A Contract covers several Locations, and a Location is covered by several Contracts.
Properties of Linked Fields with MVE
A linked field holds a reference to data from another record so that it can be used or viewed in the host table.
Records are actively linked. In a linked field, the items - records - are usually chosen by a user in response to a business process, such as selecting the Locations to which a contract applies. In other situations, linked fields are populated automatically with a saved search default, action, or rule.
Linked source records are not modified. When you link to a record in a linked field, nothing about the source record is changed. For instance, it will not be removed from another relationship, and no fields are edited.
Changes must be saved. When a user links to a record in a linked field, it will appear in the embedded table as if it is already linked, but the system does not save relationship until the containing record is saved. This behavior matches that of other types of linked fields: after choosing items from another table, you must Save the current record to confirm the selection.
Updates by rules and actions. You can apply a default value action that will populate a linked field based on a saved search. An update fields action run by a rule - such as an "All Edit Actions" rule - can also populate or update the contents of a linked field.
Searching considerations. Each field selected in a link to selected fields data type appears in the usual field list in the search block. In the advanced search wizard, these fields are moved to the bottom of the field list. For instance, if you create a Simple filter, you will see these fields in a section below the Related tables separator:
You can search on a linked field just as you would in any standard field. However, there is currently one limitation when searching: numeric fields - integer, floating point, currency, etc. in the source table cannot be searched numerically. As a result, you cannot use a search that asks whether a numeric field is greater or less than some value.
Individual fields can be included in the table view. Individual fields included in a link to selected fields can be added to the table view. Below, Sale Values is a linked field from Opportunities shown in a table view of People:
Charts and reports. Reports can use individual fields from a linked field set as the X-axis of a chart. They can also be used to group or filter charts and reports, just like fields native to the table.
Linked fields take up space and are size-limited. Unless the Yes, save space mapping option is used, linked fields take up bytes in the table, and it is unwise to set up a situation in which a linked field holds thousands of records. This can slow down the loading of record forms and otherwise negatively affect system performance.
Advantages
- Linking to a record does not modify the source record.
- Linked fields can be set or updated by rules, using an update fields action.
- Linked fields can be searched from the host table. However, numerical searches are limited.
- Individual fields from a linked field set can be displayed in the table view.
- Linked fields can be used in reports run from the host table.
Disadvantages
- The containing record must be saved in order to save changes to the linked field.
- Linked fields take up more database space and are size-limited.
Properties of Related Tables
A related table is the second half of a many-to-many relationship. While the linked field is considered active, related tables are generally passive. They rely on the relationship established by the linked field, and you must create the linked field in another table first before setting up a related table.
Once created, related tables automatically display all records that are linked to this record. For instance, if a contract includes a link to Locations Covered, we can add a related table in the Locations table showing all contracts that are linked to a particular location record.
Related tables are one of a few special data types based on the results of a saved search. They do not take up space in the table because they simply display the results of a search: they are "virtual" fields. Related tables hold any number of records with ease because they do not store any data directly. When record size or available database space in the table is an issue, related tables are a useful tool.
Source records are modified and automatically saved. When you add or remove records from a related table, you are actually resetting the linked field in those records to add or remove the value of the record you are currently editing. If you look up and "import" a single-value linked field into a related table, you are removing any value it previously had that linked it to another record. For an MVE link, you are appending the current record's value into the linked set in the other record. In order to perform these functions, a user must also have permission to edit the underlying linked field in the other table.
For instance, suppose the Contract table has a Locations Covered linked field and a related table in the Locations table showing contracts. When you use the related table's lookup icon to find and link to a contract which is not already associated with the location, what you are really editing is the Covered Locations field in that contract by adding the current location's name. Or, when you select a record and click Unlink, the system edits the contract record and removes the current value from the Covered Locations field.
Acting on records in a related table affects the source records, not the containing record. Therefore, the linked relationship is established in the related record and it is unnecessary to save the current record to confirm the change.
Limited filtering. You can filter the records that appear when users click the magnifying glass to add links, but the filters are limited to the current table and global user variables. For example, if you have a related table in the Company table for a link to the Contract table, you can't filter the related table to show contracts whose Contract Company matches the current Company. If you need to filter based on fields in the other table, you need to use a linked field.
Searching considerations. The related table field and its underlying data cannot be included in normal, dynamic searches on the table where it resides. To search based on a related table, you must use the advanced search wizard, and you are limited to using saved searches already in the related table's source table.
For instance, suppose the People table contains a related table of Opportunities and you want to search for all people with opportunities valued at more than $100,000. To do this, first create a saved search in the Opportunities table to find "Values greater than $100,000." You will then be able to create a new saved search on the People table that uses that related table filter.
Reporting. Related tables can't be included in a report. If a related table contains data relevant for reporting, the report is typically run from the source table itself. For instance, suppose you want to show all People for each opportunity. If the Opportunities table contains a related table of People, you can run the report from the People table and group by opportunity.
Related tables cannot be shown in a table view. A related table does not have specifically defined source fields that can be added to a table view.
Advantages
- Once imported or removed, records are automatically linked or unlinked and the containing record does not need to be saved.
- Related tables do not take up any database space in the table.
Disadvantages
- Selecting a record modifies the record itself.
- Related tables can't be dynamically searched from the containing table.
- Related tables can't be shown in a table view.
- Related tables can't be used in reports.
Features of Both Data Types
- New records can be created using the New button on the embedded table's action bar and the new record is automatically linked to the containing record.
- Existing records can be looked up and linked using the magnifying glass icon. The important difference is whether the containing record must be saved to confirm the changes.
- Records shown in the embedded table can be removed from the relationship using the Unlink button.
Records can be filtered to limit the records that appear in the table and in search results when users add new links, but related tables have limitations in how they can filter.
Key Questions
Think about the following questions to help determine which data type should be used in which table.
- Does one side of the relationship hold a vastly larger number of records than the other, say, over a thousand? For instance, will one contract cover thousands of assets, while each asset has only a few associated contracts? A minor difference in size is not relevant. When a large size difference exists, structure the relationship so the linked field holds fewer records and the related table holds the larger number of records.
- Which table is best suited for the manual creation of links? For instance, in a relationship between People and Opportunities, are sales staff likely to prefer adding people to an opportunity or opportunities to a person? Other factors aside, place the linked field in the table where users will actively create links or where a filter is needed to help create the linked record relationships.
- In which table are new records created more frequently? Do you need to filter which records can be selected and linked? Depending on the filter you need to create, you might need to place the linked field in the table where a filter is needed. Linked fields offer versatile filtering, while related tables can only filter records using fields in their own table and global variables.
- Will you generate any reports based on the linked data? Find out what reports are needed, then figure out which structural relationship makes it possible to build the reports. If no reports are needed or either structure works, consider other factors.
- Are users more likely to dynamically search through records in one table more than the other, using the standard table search block? When possible, put the linked field in the more frequently searched table so users can dynamically search the relevant linked fields from the main search block.
- Is automation needed to change how records are linked in either of the tables? For instance, sometimes a nightly import needs to update which records are linked to which other records because data has changed in another system. Automation can only run on a linked field, not a related table. Note: When importing, you cannot append into an MVE field, you can only update the entire contents of the field. If an import update is needed for such a field we use a different method.
- Are there space limit considerations in one of the two tables and are the links expected to hold a lot of records? Although rare, some tables with many fields can run into database limits such as maximum row or column size in the database. Related tables take up no storage space, so it is best to put the related table in the size-conscious table in that case.
- Do users want to see the linked information in the table view for one of the tables? If you need to display the information in a table view, add the linked field to that table. Related tables can't be shown in a table view.
- Do you need to use values in one table to filter the other in a third table? This is primarily relevant when working with background tables. For example, if Print Templates and Contract Types are linked to each other in a many-to-many relationship and also both linked to the Contracts table, and you need to select a contract type in the Contracts table and use the selected type to filter the list of print templates, you must place the linked field in the Print Templates table. If you need to use values from table A to filter the records in table B, table A must hold the linked field and table B must hold the related table.
Sample Scenarios
This section will review a few examples and discuss how the design factors influenced a practical decision.
People and Opportunities
In the standard knowledgebase, there is a many-to-many relationship between People and Opportunities, a table used to track potential sales opportunities at an organization. Opportunities are associated with a primary contact, but sales staff can add other relevant contacts. Of course, contacts can also be linked to multiple opportunities.
Below are the answers to the questions in the previous section for this use case:
- Each side of the relationship holds a similar number of records, and neither field is expected to hold very many records—just a few opportunities for each person and vice versa.
- Both tables are suited for manually creating links, making this a neutral factor.
- New People are added more often than Opportunities.
- There are no reports set up which need to include data from both tables.
- Users are more likely to search the People table and filter by opportunity fields, rather than filter opportunities by people. For instance, sales staff might want to find people linked to opportunities with a potential Sale Value higher than some amount and whose Status is not closed.
- No specific automation is anticipated.
- Size and space are not a concern.
- Users are more likely to add opportunity fields to views in the People table.
- These aren't background tables, so filtering based on one table's value is not necessary.
Buildout
In this case, many of the factors are neutral. The requirement to filter People by opportunity and the fact that new People are created more often led us to put the link to selected fields with MVE in the People table and a related table in Opportunities.
Below is a sample Person record showing the Opportunities link to selected fields with MVE. Note that you should usually put an input instruction above the link to selected fields to tell the user how to find and link to records.
With this structure, the available opportunities can be filtered when power users add a new person and want to link in existing opportunities. The linked set in People is filtered to a match the person's Company to the opportunity's Company.
Because the linked field is in the People table, it's also possible to construct searches based on whether a customer's linked opportunities contain certain values, such as "Status=Closed/Won" or "Status=Lost."
To complete the many-to-many relationship, the Opportunities table contains a related table called Additional Contacts.
Assets and Contracts
This next example also comes from the standard knowledgebase. Some types of contract are associated with assets, such as a support contract with an external provider to maintain hardware belonging to your organization. A single contract can apply to several assets, and assets might be covered by several contracts.
Below are the answers to the questions in the previous section for this use case:
- Neither side of the relationship is expected to hold very many records, but we projected more assets per contract than contracts per asset.
- Both tables are suited for manually creating links, making this a neutral factor.
- Although you can expect to add assets more often than contracts, there is no clear filter on which contracts should be available for which assets, another neutral factor.
- Users might want to run reports in the Contract table to find all contracts for specific asset types.
- Users would like to dynamically search through Contracts to find contracts covering particular asset types or models.
- The system required advanced automation for handling assets when a contract is renewed. The assets which were linked to one contract needed to be relinked to its renewal contract. We accomplish this with a series of rule actions that copy the renewal contract's ID into a single-value linked set - Latest Contract ID - in the asset, then sends the asset ID to the linked field in the new contract record. The automation works more efficiently with a link to selected fields with MVE of assets in the Contract table, rather than a related table of assets.
- The Contract table holds more fields, and therefore less available space, but this factor is not weighted as heavily as the automation requirements.
- Users would like to see the current contract in a table view of Assets.
- These aren't background tables, so filtering based on one table's value is not necessary.
Buildout
The strongest factor in this case was the automation required to link assets from a previous contract to its renewal record. This led to the decision to build a link to selected fields with MVE in the Contracts table called Assets in this Contract, and the Asset table contains a related table of All Contracts. Based on the requirement to show the most recent contract in an asset record, as well as the automation needs, we also include a single-value link to selected fields in the Asset table to hold the Title, ID and other information about the current contract.
In the Contract table, the Assets in this Contract linked field set is only visible when the Asset Involvement field has a value of "For One or More Assets". An input instruction tells users how to find assets and link them to the existing contract. The action bar above the embedded table can also be used to create a New asset and automatically link it to the contract.
Below is a sample asset record showing the related table of All Contracts and the Current Contract Information section. Power users can link to existing contracts in the All Contracts related table, if necessary, or use the New button on the action bar to create a new contract covering the asset.
Locations and Contracts
In this simple situation, a contract can be linked to multiple company locations that it covers, and you want to see all contracts for a given location. In this use case:
- The number of locations is small, while the number of contracts is unlimited.
- Contracts is the primary process table: users need to select the locations when filling out the contract record.
- New Contracts are created often, Locations are static data.
- All reports are run from the Contract table.
- Searching is primarily done from the Contract table.
- No specific automation depends on the Locations field.
- Size and space are not a strongly weighted factor.
- Views are not a strong factor in this case, but users seem more likely to add the location field to views in the Contract table.
- These aren't background tables, so filtering based on one table's value is not necessary.
Buildout
This was an easy decision, as nearly all the factors pointed toward the same solution: a link to single field with MVE in the Contract table called Covered Locations, and a related table in the Location table of Contracts Covering this Location.
