Using NULL to Mean "All"

{outline||<1> - |

.<1> - }

Table of Contents [Hide/Show]


   Overview
   First Generation Solution
      Approach
      Table Definitions
         UserProfile Table
         AppContact Table
         AppContactRule Table
      SQL Code
   Second Generation Solution
      Approach
      The UserEmailSetting Table
      SQL Code


Overview

We had a system with the following entities: Users, Applications, and Requests. Requests had a RequestType, and a User had a UserType based on Application. (For example, a user could be a developer for one application, but a user for another.)

We had a requirement to generate automatic emails upon certain events taking place. The rules for the emails — who to send them to and under what conditions — were identical in 90% of the cases. However, we wanted to easily handle those exceptions that represented the other 10% without having to create a record for each specific combination of UserType, Application, and RequestType. This article outlines the solution I developed.

First Generation Solution

Approach

Originally, all columns in all three tables were non-nullable. I decided to change this for the AppId column in the AppContactRule table, and use a value of NULL to indicate "all applications". Furthermore, a record for a specific (non-null) application would override a record for a non-specific (null) application. This would allow us to load our configuration for the 90% of identical cases in just a few records, yet still be able to handle the 10% of exceptions. The following SQL reflects this design intent.

Table Definitions

UserProfile Table

NameType
UserProfileIdint
LanIdvarchar(12)
Emailvarchar(100)
 

AppContact Table

NameType
UserTypeIdint
AppIdint
UserProfileIdint
 

AppContactRule Table

NameType
UserTypeIdint
RequestTypeIdint
AppIdint
EmailFlagbit

SQL Code

create view dbo.AppContactRuleExtended
as

with MyData as (
    select
         AppId          = coalesce(AppId, -1)
        ,EmailFlag
        ,RequestTypeId
        ,UserTypeId
    from
        AppContactRule
    )
select
     *
    ,MaxAppId = max(AppId) over (partition by RequestTypeId, UserTypeId)
from
    MyData

go

select
     SenderEmail    = (select Email from dbo.UserProfile where LanId = @CreatedBy)
    ,RecipientEmail = u.Email

from 
    dbo.AppContactRuleExtended m

    inner join dbo.AppContact ac
        on ac.UserTypeId = m.UserTypeId
        -- and ac.AppId = m.AppId (ac.AppId might be -1)

    inner join dbo.UserProfile u
        on ac.UserProfileId = u.UserProfileId

where 1=1
    and ac.AppId = @AppId
    and m.AppId = m.MaxAppId
    and m.EmailFlag = 1
    and m.AppId in (-1, @AppId)
    and m.RequestTypeId = @RequestTypeId

Second Generation Solution

Approach

After building the first-generation solution, we realized we wanted finer control:; we wanted users to have the ability to opt-out of emails. The same strategy of using NULL to mean "all applications" was used.

The UserEmailSetting Table

NameType
UserProfileIdint
RequestTypeIdint
AppIdint
EmailFlagbit

SQL Code

;with ExclusionsPrep as (
    select
         m.UserProfileId
        ,m.AppId
        ,m.EmailFlag
        ,u.Email
        ,MaxAppId = max(m.AppId) over (partition by m.UserProfileId)

    from 
        dbo.UserEmailSettingExtended m

        inner join dbo.UserProfile u
            on m.UserProfileId = u.UserProfileId

    where 1=1
        and m.AppId in (-1, m.MaxAppId)
        --and m.EmailFlag = 0
        and m.AppId in (-1, @AppId)
        and m.RequestTypeId = @RequestTypeId
    )
,Exclusions as (
    select *
    from ExclusionsPrep ep
    where ep.AppId = ep.MaxAppId
    and ep.EmailFlag = 0
    )
select
     SenderEmail    = (select Email from dbo.EmployeeActive where LanId = @CreatedBy)
    ,RecipientEmail = u.Email

from 
    dbo.AppContactRuleExtended m

    inner join dbo.AppContact ac
        on ac.UserTypeId = m.UserTypeId
        -- and ac.AppId = m.AppId (ac.AppId might be -1)

    inner join dbo.UserProfile u
        on ac.UserProfileId = u.UserProfileId

where 1=1
    and ac.AppId = @AppId
    and m.AppId = m.MaxAppId
    and m.EmailFlag = 1
    and m.AppId in (-1, @AppId)
    and m.RequestTypeId = @RequestTypeId
    and u.UserProfileId not in (select UserProfileId from Exclusions)