Is there a standard SQL Table design for overriding 'big picture' default values with lower level details?

Question

Here's an example. Suppose we are trying to calculate a service charge.

Say sales in the USA attract a 10 dollar charge, sales in the UK attract a 20 dollar charge

So far it's easy - we are starting to imagine a table that lists charges by country.

Now lets assume that Alaska and Hawaii are treated as special cases they are both 15 dollars

That suggests a table with states, Alaska and Hawaii are charged at 15, but presumably we need 48 (redundant) rows all saying 10. This gives us a maintainance problem, our user only wants to type 10 once NOT 48 times. It does not sit well with the UK either. The UK does not have states.

Suppose we throw in another couple of cross cutting rules.

If you order by phone there is a 10% supplement on the charge. If you order via the web there is a 10% discount.

But for some reason best known to the owners of the business the web/phone supplement/discount are not applied in Hawaii.

It seems to me that this is quite a common kind of problem and there is probably a well known arrangement of tables to store the data. Most cases get handled by broad brush answers, but there are some very detailed low level variations. They give rise to a huge number of theoretical combinations, most of which are not used.

Answer 1

Alaska and Hawaii are charged at 15, but presumably we need 48 (redundant) rows all saying 10.

No, you need three rows: two for Alaska and Hawaii, and one for the Continental United States.

All of the other rules appear to be additive. There's one record in some table for each rule. If the rule is not triggered/matched, the charge is not added.

Answer 2

One answer:

create table charge( entity char(2) , amount int ) ;

insert into charge( entity, amount)  ( 'DF', 10 ) ; -- defualt;
insert into charge( entity, amount)  ( 'AK', 15 ) ; -- alaska;
insert into charge( entity, amount)  ( 'HI', 15) ; -- hiwaii;

Then:

select coalesce( amount,( select amount from charge where entity = 'DF') )
from charge where entity = 'DC';

get you the default amount.

alternately:

select amount
from charge 
where entity 
  = ( select coalesce( entity, 'DF') from charge where entity = 'DC');

In other words, use a null result and coalesce to either replace non-existent results with a default result, or to replace a non-listed entity with a default entity.

---

Do you want a general technique/idiom, or a detailed design for a specific case? This is a general idiom.

If it's a specific case, look at what Robert Harvey said: "All of the other rules appear to be additive.". If so, your design becomes very simple, a table of charges, or (better) a table of charges, a table of jurisdictions, and a many-to-many relation. Again, this only works in an additive case;

create table surcharges ( id int not null primary key, 
   description varchar(50) not null, amount int ) ;

create table jurisdiction ( id int not null primary key, 
   name varchar(50) not null, abbr char(5) );

create table jurisdiction_surcharge ( id int not null primary key, 
  jurisdiction_id int not null references jurisdiction(id),
  surcharge_id int not null references surcharge(id) );

insert into charges (description, amount) values ( 'Outside Continental US', 15 );

insert into jurisdiction (name, abbr) values ( 'Mainland US', 'CONUS');
insert into jurisdiction (name, abbr) values ( 'Alaska', 'AK');
insert into jurisdiction (name, abbr) values ( 'Hawaii', 'HI');

insert into jurisdiction_surcharge 
 values ( jurisdiction_id, surcharge_id) values ( 2, 1 );
insert into jurisdiction_surcharge 
 values ( jurisdiction_id, surcharge_id) values ( 3, 1 );

List charges:

select a.* 
from charges a 
join jurisdiction_charge b on (a.id = b.surcharge_id) 
join jurisdiction c on (c.id = b.jurisdiction_id)
where c.abbr='AK';

Sum charges:

select sum(a.amount)
from charges a 
join jurisdiction_charge b on (a.id = b.surcharge_id) 
join jurisdiction c on (c.id = b.jurisdiction_id)
where c.abbr='AK';

Answer 3

You can group states/countries into categories and assign charges to the categories not to the states/countries.

Answer 4

I'd be inclined to allow for multiple means to identify the area where a given service charge applied like so:

Create Table ServiceCharges
    (
    Country char(3) not null                -- ISO 3166-2 alpha-3 code
    , StateOrProvince nvarchar(25) null     -- ideally a code but given multiple 
                                            -- countries, that may not be feasible
    , City nvarchar(128) null               -- again name
    , PostalCode    varchar(10) null                     
    , Rate decimal(16,4) not null               
    , Constraint CK_ServiceCharges_RateGTEZero Check ( Rate >= 0 )
    , Constraint CK_ServiceCharges_MinEntry Check ( Case
                                                    When Country Is Null 
                                                        And StateOrProvince Is Null
                                                        And City Is Null
                                                        And PostalCode Is Null Then 0
                                                        Else 1
                                                        End = 1 )
)

This could also be split apart so that the locations are maintained in a separate table with a surrogate LocationId column applied in the ServiceCharges table. This design does not account for overlap which brings the question: What should happen in the case of overlap? What happens if the USA has one charge but TX has another? Does the TX rate trump? If so, that means that the most specific location wins and we can determine specificity by the existence of a Postal Code or next a City or next a StateOrProvince or next only a country.