Navigation

	Home Page
	Index
	All Pages

Quick Search
Advanced Search »

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

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¶ Name Type UserProfileId int LanId varchar(12) Email varchar(100)

AppContact Table¶ Name Type UserTypeId int AppId int UserProfileId int

AppContactRule Table¶ Name Type UserTypeId int RequestTypeId int AppId int EmailFlag bit

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¶

Name	Type
UserProfileId	int
RequestTypeId	int
AppId	int
EmailFlag	bit

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)