SQL - Designing a Threshold value table

Question

I need to design an SQL table to be used for "threshold" or special values, which in general would have the following properties: - key - value - comparer (less than and greater than as a start, preferably with the ability to add additional types of comparers or rules in the future)

As a basic use case example, these threshold values would be used to raise an event if some parameter in the system exceeds a threshold.

With that in mind, I would appreciate any feedback or input in how precicely to go about this. In the past I have seen such a task done by either using a lookup table containing the types of the comparers, or storing some type of formula in a column and afterwards parsing it using NCalc (.NET) or something similar. Both approaches have drawbacks- the lookup table doesn't seem very practical, and parsing stuff seems very unstable.

Any suggestions on what would be sensible in this scenario would be greatly appreciated. I am not an expert in DB design, so any input will be valuable.

Answer 1

For a database design, all one needs is the left, operator, and right.

Something like this:

X > 10

A table like this:

RuleSetID   PropertyName    Operand TargetValue 
1            Weight          LessThan   200

Then one can use System.Linq.Expressions namespace to dynamically create expressions for evaluation.

For more information on this .Net framework feature, please visit this link:

https://msdn.microsoft.com/en-us/library/system.linq.expressions(v=vs.110).aspx

All kinds of operators are supported, greater than, less than etc.

Now, some code to compile the expressions:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using RulesEngine.Model;
using System.Diagnostics;

namespace RulesEngine.Engine
{
    public class ExpressionBuilder
    {
        public Func<T, bool> Compile<T>(Rule rule, Type type)
        {
            try
            {
                var param = Expression.Parameter(type);
                Expression expression = BuildExpression<T>(rule, param);
                return Expression.Lambda<Func<T, bool>>(expression, param).Compile();
            }
            catch (Exception exception)
            {
                //We would want to log this failure so the rule can be corrected.
                Debug.WriteLine(exception.ToString()); 
                //Return an expresssion that would always return false
                Expression<Func<T, bool>> defaultLambda = x => false;
                return defaultLambda.Compile();
            }

        }

        private Expression BuildExpression<T>(Rule rule, ParameterExpression param)
        {
            var left = MemberExpression.Property(param, rule.PropertyName);
            var propertyType = typeof(T).GetProperty(rule.PropertyName).PropertyType;
            ExpressionType binaryExpressionType;

            if (ExpressionType.TryParse(rule.Operand, out binaryExpressionType))
            {
                var right = Expression.Constant(Convert.ChangeType(rule.TargetValue, propertyType));
                return Expression.MakeBinary(binaryExpressionType, left, right);
            }
            else
            {
                var methodName = propertyType.GetMethod(rule.Operand);
                var parameterType = methodName.GetParameters()[0].ParameterType;
                var right = Expression.Constant(Convert.ChangeType(rule.TargetValue, parameterType));
                return Expression.Call(left, methodName, right);
            }
        }
    }
}

A Rule would be a class to hold the definition from the database.

using System;
using System.Collections.Generic;
using System.Linq;

namespace RulesEngine.Model
{
    public class Rule
    {
        public string PropertyName { get; private set; }
        public string Operand { get; private set; }
        public string TargetValue { get; private set; }

        public Rule(string propertyName, string operand, string targetValue)
        {
            PropertyName = propertyName;
            Operand = operand;
            TargetValue = targetValue;
        }
    }
}

Now we create an object to hold the instance data. (Replace foo with your threshold instance data).

public class Foo
{
    public string Name { get; private set; }
    public int Weight { get; private set; }
    public int Size { get; private set; }

    public Foo(string name, int weight, int size)
    {
        Name = name;
        Weight = weight;
        Size = size;
    }
}

And finally putting it all together...comparing instance data with the rule.

private static void RunExample()
        {
            var rule = new Rule("Weight","Equal", "100");
            var foo = new Foo("FooName", 100, 200);


            var expressionBuilder = new ExpressionBuilder();
            Func<Foo, bool> fooRule = expressionBuilder.Compile<Foo>(rule, foo.GetType());

            Console.WriteLine("Is weight equal to 100 for foo?" + fooRule(foo)); //true
        }

Since it's generic you can use it as a mini rules engine that can take any instance data and compare it against a set of rules to return true/false.