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.Hi
It is not clear why you are checking PRODUCT and MARKET if you want a rule
applicable to 'CA' And why you ignore R1 for CA?
Rule Type Values
R1 State All
R1 Product All
R1 Market All
R2 State CA
R2 State NY
R2 Product DOL
You could also make Type a lookup table and use the key value in your table.
Your queries will be simplified if you do not have the 'All' option. To keep
your existing code and use the new table you could create a view
John
"unklevo" wrote:

> 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.
>|||Thank you for your response John.
I made a mistake in my table data.
It should look like this.
TABLE: RULE
--
RULE_ID STATE PRODUCT MARKET
--
R1 ALL ALL ALL
R2 NY DOL NULL
R3 CA NULL NULL
The reason I am checking for MARKET and PRODUCT for 'CA' is that if I
fill in the product for R3 record
R3 CA DOL NULL
than using just a state='CA' is not sufficient.
All 'CA' accounts that have product other than 'DOL' will use rule R1
but accounts with state='CA' and PRODUCT='DOL' will use R3.
In set-up that you are offering can you give me an example of your
query where you want to find rule for state='NY' and product='DOL'and
market='A'
Thanks,
Igor.|||Hi
One Way:
SELECT DISTINCT R.Rule
FROM MyRules R
WHERE EXISTS ( SELECT * FROM MyRules S WHERE S.Type = 'State' AND
S.Value = 'CA' AND R.Rule = S.Rule )
AND EXISTS ( SELECT * FROM MyRules P WHERE P.Type = 'Product' AND
P.Value = 'DOL' AND R.Rule = P.Rule )
AND EXISTS ( SELECT * FROM MyRules M WHERE M.Type = 'Market' AND
M.Value = 'A' AND R.Rule = P.Rule )
Or
CREATE VIEW MyRulesView AS
SELECT R.Rule, S.Value AS State, P.Value AS Product, M.Value AS Market
FROM MyRules R
FULL OUTER JOIN MyRules S ON R.Rule = S.Rule AND S.Type = 'State'
FULL OUTER JOIN MyRules P ON R.Rule = P.Rule AND P.Type = 'Product'
FULL OUTER JOIN MyRules M ON R.Rule = M.Rule AND P.Type = 'Market'
SELECT Rule, State, Product, Market
FROM MyRulesView
WHERE State = 'CA'
AND Product = 'DOL'
AND Market = 'A'
John|||Can you explain in terms of a logical data model why you think that
state, product and market are values and not attributes that take on
values. See the problem? This is an EAV design, which is always a
disaster.