Database Storage of Formulas with Variables

Question

This is a little complicated, so bear with me.

My employer is asking to build a system that calculates task iterations from a set of variables.

Each task in a project has a specific formula used to calculate the number of times the task needs to be accomplished ("iterations"). The formula could rely on constants, variables (number-entered, so a task might be related to the number of suppliers we have or the number of offshore employees, and these values may be different for each project), and/or time (e.g. once per month, once per quarter, once per 6 months, once per year)

How can I design a database to hold these formula so that I can calculate them later?

Example formulas:
Once per month per supplier.
Twice per quarter per supplier per customer.
Once per month for every six suppliers.

Tech stack is C#, .NET 3.5, SQLServer 2005.

Answer 1

Could you create defined set of Verbs/placeholders which you use to process a varchar column which uses the verbs/placeholders to define the formula?

These verbs/placeholders are then used by some program, to process the formula, working out what it's trying to do technically.

So:
Once per month per supplier could be:
1 / MONTH / SUPPLIER
Twice per quarter per supplier per customer could be:
2 / QUARTER / SUPPLIER / CUSTOMER
etc.

You would need to only handle a limited number of formula templates, though, otherwise this would become a mess.

If you don't need to be massively flexible this approach could work, however if you want to be completely flexible, you're moving into the self created report type, which is much more complex.

Answer 2

What do you think about this technique? (using JScript)

Evaluate a formula at runtime

using System;

namespace CodeFreezer.CodeSamples
{
    public static class ExpressionEvaluator
    {
        /// <summary>
        /// Evaluate
        /// Evaluate a math expression
        /// </summary>
        /// <param name="expression">Expression to evaluate</param>
        /// <returns>result</returns>
        public static double Evaluate(string expression)
        {
            if (String.IsNullOrEmpty(expression)) throw new ArgumentNullException("expression");

            return Convert.ToDouble(Microsoft.JScript.Eval.JScriptEvaluate(expression, Microsoft.JScript.Vsa.VsaEngine.CreateEngine()));
        }
    }
}

Answer 3

I'd store them like this:

Once per month per supplier.

(* month supplier)

Twice per quarter per supplier per customer.

(* 2 (* quarter (* supplier customer)))

Once per month for every six suppliers.

(* month (/ (+ supplier 5) 6))

Easy to parse. After you parse, you present a list of identifiers you find and ask the user to give them values.

Answer 4

Have a look at NCalc (Codeplex) You can embed that parser in SQLCLR, and save your formulas in tables.

Answer 5

How about some dynamic SQL?

1. Create a table with library of formulas (procedures or sql scripts) generic enough to cover your cases. For formula parameters use placeholders like '#number of iterations here#'. Formula column is nvarchar(max) -- strings.

2. Create table of task steps, for each step point to function to run and list of parameters to use.

3. Dynamically build function script (text) replacing placeholders with actual parameters (search & replace).

4. Execute functions using sp_executesql N'my_funcion_text_here'

Answer 6

Was NCalc embedded in SqlClr actually used? I tried this and cannot get this to work due to security issues calling into NCalc from SQL.

I get this exception "Attempted to perform an operation that was forbidden by the CLR host."

Can you describe how you were able to implement this?