Sparsely populated lookup table in SQL

Question

I'm trying to write a lookup method for determining a SMS message to send to a user based on a few parameters associated with the user/system. We will have a default message that will be used as a last resort, but there are multiple ways to override the message by various parameters. Here is what I have so far for the lookup query - are there any better ways to do this? Maybe a lookup is not the right approach for this?

Here is the lookup table:

MessageLookup
{
ID bigint PK                          
Key varchar                           
CampaignTypeID bigint FK,             
ServiceProviderID bigint FK nullable, -- optional override parameter
DistributorID bigint FK nullable,     -- optional override parameter
CampaignID bigint FK nullable,        -- optional override parameter
Message varchar                   
}

Here is an example of what the table would look like:

   ID Key  CTID SPID DistID CampID Message
    1 Help 1    NULL NULL   NULL   'This is the default message'
    2 Help 1    375  NULL   NULL   'This is the SP375 message'
    3 Help 1    377  NULL   NULL   'This is the SP377 message'
    4 Help 1    NULL 13     NULL   'This is the Dist13 message'
    5 Help 1    375  13     NULL   'This is the SP375/Dist13 message'
    6 Help 1    NULL 13     500    'This is the Dist13/Camp500 message'
    7 Help 1    375  13     500    'This is the SP375/Dist13/Camp500 msg'
    8 Help 1    NULL NULL   500    'This is the Camp500 help message'

Here is the query I have:

select
    --top 1
    *
from MessageLookup ml
where ml.[Key] = @Key
and ml.CampaignTypeID = @CampaignTypeID
and
(
    ml.ServiceProviderID = @ServiceProviderID or
    ml.ServiceProviderID is null
)
and
(
    ml.DistributorID = @DistributorID or
    ml.DistributorID is null
)
and
(
    ml.CampaignID = @CampaignID or
    ml.CampaignID is null
)
order by
    CampaignID desc, -- highest precedence lookup param
    DistributorID desc,
    ServiceProviderID desc -- lowest precedence lookup param

Answer 1

I think that's a valid approach, easy to extend, intent is pretty clear, and you can tidy the sql by doing the following

select
    --top 1
    *
from MessageLookup ml
where ml.[Key] = @Key
and ml.CampaignTypeID = @CampaignTypeID
and ml.ServiceProviderID = IsNull(@ServiceProviderID, ml.ServiceProviderID)
and ml.DistributorID = IsNull(@DistributorID, ml.DistributorID)
and ml.CampaignID = IsNull(@CampaignID, ml.CampaignID)
....

Answer 2

I'm not sure what the best way is, but here are some alternatives:

One thought would be to store a pattern with each rule, like so:

ID Key CTID Rule        Message
1 Help 1    '[%:%:%]'    'This is the default message'
2 Help 1    '[375:%:%]'  'This is the SP375 message'
3 Help 1    '[377:%:%]'  'This is the SP377 message'
4 Help 1    '[%:13:%]'   'This is the Dist13 message'
5 Help 1    '[375:13:%]' 'This is the SP375/Dist13 message'

and then use a LIKE test instead of all the ANDs.

Another thought would be to use OUTER JOINS.

Or (playing off the answer that just came in) to DRY things up further by writing:

where ml.[Key] = @Key
  and ml.CampaignTypeID = @CampaignTypeID
  and IsNull(ml.ServiceProviderID = @ServiceProviderID,true)
  and IsNull(ml.DistributorID     = @DistributorID,    true)
  and IsNull(ml.CampaignID        = @CampaignID,       true)

Answer 3

What you are doing makes sense, and works. If you are after best practices -- don't use "SELECT *" -- enumerate which columns you are selecting.

Answer 4

I think I would design the database in a different way, with one table TA that would be (MSGID, Key, CTID, Message) and another one TB that would store (MSGID, ID, IDTYPE) where ID would represent CampID/DistId/DefaultId (indicated by IDTYPE), the PK of which should be (ID, IDTYPE, MSGID) in this order. You can assign to IDTYPE a numerical value that indicates priority, with 0 for the default (and a matching ID of 0). All columns are NOT NULL.

If I understand well your problem, your input is made of three values x, y and z (plus an implicit 0 in my case), you want to return the message for which you have the most matches, and, in case of equality, order by IDTYPE.

select MSGID, count(*) as nbr_candidates, max(IDTYPE) as priority
from TB
where (ID = x and IDTYPE = ...)
   or (ID = y and IDTYPE = ...)
   or (ID = z and IDTYPE = ...)
   or (ID = 0 and IDTYPE = 0)
group by MSGID
order by 2 desc, 3 desc

should return the "best message" as its first row, and all you need to add is a

top 1

then join with the other table. This is likely to be faster than a one-table solution, because table TB only contains numerical IDs and will be quite compact, and the join will be instant.