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
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
[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
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