Many to Many UNIQUE Relationships, Master Data Service (MDS) 2014

Hi,

This is a quick post, to illustrate about a challenge where I had to manage M2M relationship in MDS 2014.

Well this issue has been taken care off in MDS 2016 version which you can read about in the post.

But if you are stuck in the 2014 MSSQL world and wanted a reliable solution with effective checks in place. Here’s the model I adapted to build the above with relevant checks in place

Now if you look at my MDS entities, I have the above mentioned 3 entities

• Student
• Teacher
• StudentTeacher (Link entity)

Now if you observe, I have a filed in the entity (StudentTeacher) termed as “StudentTeacherCode”

This is nothing but a concatenated value of the Student-Teacher (separated by a hyphen) name fields, for which I am using the MDS business rules, and post which I validate on the same column that I have unique relationships.

The business rules in action are as follows:

• Rule for Populating fields with computed values
  

  

• Rule for validating the computed field to be unique
  

  

And that’s it !!!

Now as soon as I have duplcate M2M relationships in my MDS 2014, I have a failed validation to alert business users some things not right !!!

And the same I can filter it out in the MDS backend with the following view and query

SELECT * FROM mdm.vw_StudentTeacher
WHERE ValidationStatus = N'Validation Succeeded' /*Validation Failed*/

Hope it helps…