1

I need to implement user risk scoring in model

  1. The user risk scoring must compile dynamically on changes (eg: profile, transaction) - meaning for any changes on profile or any incoming transaction, the new risk scoring value needs to be be able to reflect immediately

  2. It will span across 10+ different tables

  3. The query with user list need to be able to filter by RiskScore

Options

  1. I can create a user defined function [udf].[CalculateRiskScore] and make a computed column in [dbo].[User] or query out during view The advantage with this is good maintainability but bad performance.

  2. Another way is to have a trigger for those 10 tables to call [udf].[CalculateRiskScore] and update [RiskScore] into [dbo].[tblUser] with indexing on [RiskScore]. The advantage with this is good performance but poor maintainability since now i have to repeat x times depending on how many table that will affect [RiskScore].


There may be another solution I am not aware of. Is there any way that I can get the best of both worlds while adhering to the requirement?

My objective is to call [udf].[CalculateRiskScore] in one single place only while not sacrificing performance of filtering user with [RiskScore]

c448548
  • 375
  • 1
  • 4
  • 13

0 Answers0