Database design question

I am trying to find more elegant design solution for the following
scenario:
TABLE: RULE
--
RULE_ID STATE PRODUCT MARKET
--
R1 ALL ALL ALL
R2 NY DOL NULL
R2 CA NULL NULL
In front-end application we need to know what rule to use based on what
state, product and market we are using at the moment.
Selection Criteria for the rules might expand (ex. in the future we
might add EMPLOYEE column to the RULE table)
In this set-up if I need to know what rule to use for the state OF CA
than my query would be as following:
SELECT RULE_ID
WHERE STATE='CA' and PRODUCT is NULL and MARKET is null
That would return me the R3.
For all others default with ALL values would be used.
"ALL" is not a valid state so there is some hardcoding required to
return this default record.
Is it better to repeat all states and all products and all markets in
the RULE table?
Is there way to avoid changing table structure every time I add new
criteria for selecting the rules?
I hope I was clear enough...
Thanks in advance.This is a SQL version of a decision table with NULL as the "don't care
token". I would replace 'ALL' with NULL and write the query as:
SELECT rule_id
FROM Rules
WHERE state = COALESCE (@.mystate, state)
AND product_code = COALESCE (@.myproduct, product_code)
AND market = COALESCE(@.mymarket, market);
If more than one rule applies, you will get all of them back. You will
always have R1 in the set. If you must pick one and only one rule,
with R1 as the default, then use:
SELECT MAX (rule_id)
FROM Rules
WHERE state = COALESCE (@.mystate, state)
AND product_code = COALESCE (@.myproduct, product_code)
AND market = COALESCE(@.mymarket, market)
HAVING COUNT(*) = 2;
This means that you have one rule and R1.|||Celko,
Thank you for offering this solution.
I think that this is more elegant than the one I and John were
offering.
I will try to implement it.
Thanks again,
Igor.