Database design for conditional actions

Question

I'm working on something of a wizard-type application to allow users to build simple "scripts" that basically perform actions based on certain conditions. Scripts that they build will be stored in a database, and modification will be common, so some sort of forward-only text generation is not an option. My program converts this internal database structure to the actual script output I need, so I will just be re-generating the output whenever they modify their script.

I'm looking for some advice about a good database structure to be able to store this information in. I have one working at the moment, but I'm just curious if I missed anything obvious that would make it a little cleaner. Any suggestions are appreciated.

To give some more detail, here's an genericized example of the type of "script" a user can create through picking conditions and actions in the GUI:

if ($variableA == 100 && $variableB > 25 && $variableC < 10)
{
    performAction();
    performAnotherAction();
    if ($variableC == 0)
    {
        performYetAnotherAction();
    }
    else if ($variableC == 1 || $variableC == 2)
    {
        performEvenMoreActions();
    }
}
else
{
    performDefaultAction();
}

Some notes about what is and isn't possible, just so that it's clear:

• "if" conditionals can have any number of "else if" conditionals attached, as well as an optional "else".
• each conditional can have any number of "tests" ($variableA == 100, etc), however every test can be considered to be represented as (<variable>,<operator>,<test value>), there's no need to worry about more complex conditions.
• even though each conditional can have any number of tests, they will always be joined by the same boolean operator. That is, if there are multiple tests in a conditional, they are either always joined by &&, or always joined by ||, there is no mixing.
• Conditionals can be nested infinitely, so some sort of hierarchical structure is necessary.
• Inside conditionals there may be any number of actions, which must be performed in the same sequence they are defined in. Actions can simply be represented as a function name, there's no need to worry about any other "action types".

Answer 1

Whenever I've had to store / manipulate something "code-like" I've always ended up going down the XML route.

The main reason being that expressing and then calculating something like (a and b and (c or (d and e))) is a lot easier in a hierarchical structure.

In your example the nesting is the conditionals so something like. (Very rough, just to give an idea)

<if>
    <expression />
    <true>
        <action />
    </true>
    <false>
        <if>
            <expression />
            <true>
                <action />
            </true>
        </if>
    </false>
</if>

If you're using SQL2005+ it has it's HierarchyId data type, you can use this instead of an XML format to maintain the hierarchy, its a lot more useful for getting all data related to a node, etc.

Note: This is by no means intended to be a full answer or even a partial answer, just throwing a few experiences out there.

Answer 2

You haven't described any requirement for fetching or searching fragments of scripts using SQL, so there's no need to decompose the script.

So I wouldn't try to store the script cleverly in a database. Instead, just store the whole script in a text BLOB, and treat a script as a single atomic value with respect to the storage of it.

Any manipulation you do to the script will be done in your application, so you need a parser. So choose a simple syntax that's easy to parse. I suggest either Python, XML, or your own Domain-Specific Language.

FWIW, I have plenty of experience both with databases and with parsers. This isn't a very hard task, it's just completely unnecessary, as far as you've described your project.

Bottom line: code is code, and data is data.

Answer 3

You can represent each node with a reference to its parent and then join against the parents to get the children back out. This is a standard method of representing hierarchical structures in a relational database.

Alternatively, if each of the rules is discrete you can represent it in a textual or XML format and just store the rule in a blob. If you are processing a large number of these rules you might want to consider using an off-the-shelf Rete-derivative based rules engine like Ilog.