I need to implement user risk scoring in model
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
It will span across 10+ different tables
The query with user list need to be able to filter by
RiskScore
Options
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.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]