Other M2M relationships

Perhaps the most popular examples of many-to-many tables, are the sys_user_has_role and sys_user_grmember tables. These tables respectively maintain relationships between users and the roles that they have, and between users and the groups of which they are members. These M2M tables are not defined in the sys_m2m table, but are specially made by ServiceNow.

The sys_m2m table is probably the easiest way to define a many-to-many relationship, but can you think of another way to do so? That is, to create a relationship in which the left-side record can be related to an arbitrary number of right-side records, which can in turn be related to an arbitrary number of left-side records?

In terms of using a field to relate one record to one other, a Reference field is pretty effective; however, that isn't the only type of field that can accept a FK value in ServiceNow. To see an example of a List field type, let's navigate to the sc_catalog table. This table stores information about the service catalogs in your instance.

On the sc_catalog table, open the Service Catalog record, and you'll see a field in the left column called Editors, but instead of an input field, you'll see a lock icon:

This is a List field type, and it is a type of Reference field. In this case, the reference points to the User (sys_user) table. Clicking the lock icon opens up the field for editing. You can enter one value after another, and the field will store them in the database column as a comma-separated list of Sys IDs. You might say that this is not technically an FK column in the database, but more like a multi-FK column. It contains multiple PKs (Sys IDs).

It would be technically possible to put one of this sort of field on one table (table A) and another on table B, and thus have a pseudo-many-to-many relationship between these two tables. In practice, this is wildly impractical, but just serves to demonstrate that there are few things that can't be done in ServiceNow!