locked
Designing tables for rules with default values and exceptions RRS feed

  • Question

  • What is the best way to design tables for rules with default values and exceptions?

    For example, let's say I need to store the following logic:

    For all other cases, the result is NULL.
    These rules change from time to time, and there are many possible values for each condition.

    Tuesday, April 23, 2013 10:35 AM

Answers

  • The approach I have used is to have a table like this:

    CREATE TABLE conditions (
       id int IDENTITY PRIMARY KEY,
       cond1 char(1) NULL,
       cond2 int NULL,
       cond3 char(1) NULL,
       result int NOT NULL,
       UNIQUE (cond1, cond2, cond3)
    )

    To find the value for a specific combination I have done this:

    SELECT TOP 1 @result = result
    FROM   conditions
    WHERE  (cond1 = @cond1 OR @cond1 IS NULL)
      AND  (cond1 = @cond2 OR @cond2 IS NULL)
      AND  (cond3 = @cond3 OR @cond3 IS NULL)
    ORDER BY cond1 DESC, cond2 DESC, cond3 DESC

    So the condition for result = 2 would be:

    cond1 = NULL, cond2 = 1, cond3 = A

    For Result = 11, there would be three rows:

    cond1 = NULL, cond2 = 1, cond3 = C
    cond1 = NULL, cond2 = 1, cond3 = G
    cond1 = NULL, cond2 = 1, cond3 = H

    The ORDER BY clause implies a priority between the conditions that may not work for you. Overall, the table table builds on a certain mind-set: the NULL row applies, unless there is a specific one.



    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Proposed as answer by Kalman Toth Thursday, April 25, 2013 9:59 PM
    • Marked as answer by Alex.Friedman Friday, April 26, 2013 8:20 PM
    Thursday, April 25, 2013 9:48 PM
  • Yes, there was an error in my query, and yours is correct. I was looking at query we have in our system, but I added the extra @ as that is a common patterin in dynamic searches. My apologies.

    As for having two tables, I guess depends on that the point of these tables are. We have a few tables like these and the purpose of these is to define a fee model, discount, refund or similar. For instance, take customers, customer categories, products and product categories. A customer category may have a certain discount on a product category. But a certain customer may a higher discount for a specific product in that category. You talked about defaults and exception, I would call it a priority table. It might still be the same thing.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Alex.Friedman Friday, April 26, 2013 8:20 PM
    Friday, April 26, 2013 7:50 PM

All replies

  • I suggest the following encoding:

    ALL                   ~

    All but U            !U

    C, G & H            CGH


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Tuesday, April 23, 2013 3:29 PM
  • Hmm, I'm not sure I understand how to query that.

    If a stored procedure receives the values of the conditions and needs to return the result, how would it work?

    Tuesday, April 23, 2013 3:37 PM
  • Hi Alex,

    The stored procedure can implement the logic using IF ELSE and/or CASE conditionals.

    Example:

    DECLARE @Code varchar(10) = '!U';

    IF @Code = '~'

    BEGIN

    ....

    END

    ELSE IF LEFT(@Code,1) = '!'

    BEGIN

    ....

    END

    ELSE.....



    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Tuesday, April 23, 2013 4:20 PM
  • Okay, let's say the SP receives the following parameter values:

    @Condition1 = 'S'
    @Condition2 = 1
    @Condition3 = 'H'

    How would the SP query the table to get the correct result?
    Do you really mean to use string manipulations to extract and compare the values?

    I must say this goes against every fiber of my being -- I'd try for something more normalized.

    Wednesday, April 24, 2013 7:54 AM
  • Google up the term "decision table"; it is a tool meant for this kind of programming. SQL is not meant to do this; it is DATA base, not a busienss rules tool.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Proposed as answer by Naomi NEditor Friday, April 26, 2013 7:56 PM
    Wednesday, April 24, 2013 7:14 PM
  • Thanks, the "decision table" search term was what I needed.

    Am I correct to understand that the best way to handle it is to store it in the DB in a pretty much non-relational way, and parse in the application?

    Perhaps HierarchyID can help if a hierarchy structure is what's needed here?

    Thursday, April 25, 2013 4:36 PM
  • Alex,

    There are two issues:

    1. Representation of business rules like in a decision table

    2. Programming logic to carry out the business rules, T-SQL or programming language

    hierarchyid implementation of the decision table may be a good way to go:

    http://msdn.microsoft.com/en-us/magazine/cc794278.aspx


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Thursday, April 25, 2013 5:50 PM
  • The approach I have used is to have a table like this:

    CREATE TABLE conditions (
       id int IDENTITY PRIMARY KEY,
       cond1 char(1) NULL,
       cond2 int NULL,
       cond3 char(1) NULL,
       result int NOT NULL,
       UNIQUE (cond1, cond2, cond3)
    )

    To find the value for a specific combination I have done this:

    SELECT TOP 1 @result = result
    FROM   conditions
    WHERE  (cond1 = @cond1 OR @cond1 IS NULL)
      AND  (cond1 = @cond2 OR @cond2 IS NULL)
      AND  (cond3 = @cond3 OR @cond3 IS NULL)
    ORDER BY cond1 DESC, cond2 DESC, cond3 DESC

    So the condition for result = 2 would be:

    cond1 = NULL, cond2 = 1, cond3 = A

    For Result = 11, there would be three rows:

    cond1 = NULL, cond2 = 1, cond3 = C
    cond1 = NULL, cond2 = 1, cond3 = G
    cond1 = NULL, cond2 = 1, cond3 = H

    The ORDER BY clause implies a priority between the conditions that may not work for you. Overall, the table table builds on a certain mind-set: the NULL row applies, unless there is a specific one.



    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Proposed as answer by Kalman Toth Thursday, April 25, 2013 9:59 PM
    • Marked as answer by Alex.Friedman Friday, April 26, 2013 8:20 PM
    Thursday, April 25, 2013 9:48 PM
  • Thanks, Erland, this is much closer to what I'm looking for.

    Basically you're saying to use a single table, and to use NULL for the defaults, and specific values for the exceptions -- giving a priority to the specific values.
    Do you think there's any point to separating it into two tables -- one for the defaults and another for the exceptions?

    Also, just one thing about the suggested query is that @condX will never be NULL.
    So, if what's sent to the SP is:
    @cond1 = 'S'
    @cond2 = 1
    @cond3 = 'H'

    Then we'll need something like (removed the @ from the NULL parts):

    SELECT TOP 1 @result = result
    FROM   conditions
    WHERE  (cond1 = @cond1 OR cond1 IS NULL)
      AND  (cond1 = @cond2 OR cond2 IS NULL)
      AND  (cond3 = @cond3 OR cond3 IS NULL)
    ORDER BY cond1 DESC, cond2 DESC, cond3 DESC

    Correct?

    Friday, April 26, 2013 6:45 AM
  • Yes, there was an error in my query, and yours is correct. I was looking at query we have in our system, but I added the extra @ as that is a common patterin in dynamic searches. My apologies.

    As for having two tables, I guess depends on that the point of these tables are. We have a few tables like these and the purpose of these is to define a fee model, discount, refund or similar. For instance, take customers, customer categories, products and product categories. A customer category may have a certain discount on a product category. But a certain customer may a higher discount for a specific product in that category. You talked about defaults and exception, I would call it a priority table. It might still be the same thing.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Alex.Friedman Friday, April 26, 2013 8:20 PM
    Friday, April 26, 2013 7:50 PM
  • All right, thanks!
    Friday, April 26, 2013 8:21 PM