What purpose does the sys_m2m table actually serve?

[Stephen W_]

I understand it is used to create many to many relationships..

However, the first step for creating a m2m relationship, is to create an intermediary table.

I've created a table which has only two distinct fields, just location1 and location2 reference fields.

I've added the related object(red items in the slushbucket) to the form.   I've also added the related list to the bottom of the form.

I can add locations to a location using this related object form to associate the locations.

I haven't done anything with the sys_m2m table yet, because I fail to see what value it adds.

Can anyone enlighten me?

Thanks

[Subhajit1]

The sys_m2m table provides a system provided way to create m2m relationship tables instead of going the way of Table Creators.

[tltoulson · Accepted solution]

Hi Steve,

First and foremost, in the wiki article Creating a Many-to-Many Relationship - ServiceNow Wiki, ServiceNow clearly states that for new Many to Many relationships you should always use the M2M Definitions table.   I would not take that lightly.   ServiceNow could be doing any number of things under the hood with Glide Objects or DB Objects for any number of reasons.   The point is we don't really know but we do know that ServiceNow supports M2M relationships via the M2M Definitions table.

On a more practical note, the M2M relationship definition method creates a related list that by default uses the "Edit" button slush bucket.   In most cases, the preferred behavior on a many to many relationship is to select from a slush bucket rather than create new records.

So yes, you can create a table that references two tables but that isn't truly a M2M relationship in ServiceNow terms.   To create a ServiceNow M2M relationship, use the sys_m2m table.

I hope this helps!

[Uncle Rob]

I've always disagreed with SN on this.   I only create M2M's via the sys_m2m table when I want to leverage the "invisible middle" interface.   If I want something to work like Groups and Users where selecting users creates the middle record, that's when I use "formal" M2M via sys_m2m.

But other times I want the middle layer to be more robust than a simple representation of a relationship.   Sometimes the relationship itself will have properties, and those properties will need to be entered at creation.   In those circumstances it makes far more sense to create a "manual m2m".

There's two distinct use cases that require two approaches.

EDIT:   Woah!   Sorry to go thread-zombie on you NowBro!

[tltoulson]

Completely agree, rfedoruk!   I was about to respond to Blaine's post but it seems to have disappeared.   But one of his points was that m2m is just a pattern and ServiceNow's sys_m2m is just an implementation of that pattern and he was spot on.

I have actually had valid use cases for using JSON objects in string fields and double GlideLists over separate tables to implement a many 2 many pattern.  

I tend to use sys_m2m for the invisible middle as you indicated and I tend to use a manual table if the relationship is important as an entity.   I forgot about the other approaches because most of my use cases have entailed the invisible middle.

No worries on Zombie thread, someone is bound to read this thing at a later date and where would the conversation be without the points you added!