Persisting boolean logic

Question

It is a kind of design related question rather than technical question. Title may not be perfect feel free to edit.

I have a requirement as below: I want to save qualifications for a particular jobEntity(not to be confused with SQL Server job) in SQL Server 2012 from Table. For the same purpose I have another table say qualifications, which saves above qualifications against job which has a referential integrity with job table. The stored procedure for making this has parameter JobID and User defined table type of schema:

CREATE TYPE [dbo].[TableTypeQualificationJob] AS TABLE(
    [QualificationID] [int] NULL,
    [QualificationCriteria] [nvarchar](4000) NULL,
    [OptionTo] [bigint] NULL
)
GO

I have corresponding entity in business layer. So when user creates multiple qualifications for job he passes List to function, and the underlying methods converts list in to datatable and is added in databases using stored procedure.

The catch is the qualifications can in OR or AND. e.g if there are 5 qualifications say qualification1 qualification2 qualification3 qualification4 qualification5

and enforced to the job as

qualification1 OR qualification2 OR qualification3 AND qualification4 AND qualification5

So its making three groups

(qualification1 OR qualification2 OR qualification3) AND (qualification4) AND (qualification5)

So how can I interprete it in QualificationEntity class and Database. I am calling SP using ADO.NET by manually converting list to DataTable for user-defined table type, how can I implement it?

Answer 1

I would store the OrQualifications and AndQualifications as separate entities and keep them both as many-to-many references with the Job entity, this way you can differentiate the qualifications.

To se if an applicant has the right qualifications, you check that ALL the qualifications in the AndQualification entity is met, AND that ANY of the qualifications in the OrQualifications entity is met.

---

Edit:

The above/initial answer was meant to satisfy a requirement of one list of qualifications that were required and another where at least one was required. To me this seems good enough, are you sure you really need nested qualifications? Trust me, making an application more complex than it needs to be is a bad idea.

Expression tree
Nested qualifications is another beast and here is one suggested solution: The expression is stored as a full binary tree (a binary tree where each node has exactly 0 or 2 children). All nodes in this tree consists of operators AND or OR (& or |) except for the leafs which will be the qualifications.

Example expression:
(A | B & C) | (D & E)

(The conversion from an expression to a tree is entirely dependent on how you input data, where the easiest would be to simply create the tree manually!)

As a tree:

      |
   /     \
  |       &
 / \     / \
A   &   D   E
   / \
  B   C

You have a number of different choices on how to persist this tree to a database, e.g:

• A hierarchial model where the absolut minimum is two columns, one Id and one ParentId. To fetch the tree you need a recursive query. This option can be pretty demanding for a relational database.
• Serialize the tree as e.g. xml (XmlSerializer) or json (json.NET) and save it as text.

Personally I would choose the serializing option as you will always need full trees and it's easy to deserialize into a datastructure.

Here is an example datastructure:

public class Node
{
    public Node LeftChild { get; set; }
    public Node RightChild { get; set; }
}

class OperatorNode : Node
{
    public bool IsAnd { get; set; }
}

class QualificationNode : Node
{
    public bool IsQualificationMet { get; set; }
}

Then you need a function that can parse this tree and output true or false:

public bool EvaluateNode( Node node )
{
    var qualificationNode = node as QualificationNode;
    if ( qualificationNode != null )
    {
        return qualificationNode.IsQualificationMet;
    }
    var operatorNode = node as OperatorNode;
    if ( operatorNode.IsAnd )
    {
        return EvaluateNode( node.LeftChild ) && EvaluateNode( node.RightChild );
    }
    return EvaluateNode( node.LeftChild ) || EvaluateNode( node.RightChild );
}

Disclaimer: This is so called quick and dirty code, please make something better.

Other options
Look into System.Linq.Expressions.Expression. It can be used to programmatically build logic, so perhaps this is something you can use.